Other REPLICATE SQL function

1. Usage of REPLICATE

SQL Server Query 1

            
 SELECT TOP 15  

     c.CustomerID,  

     c.CompanyName,  

     REPLICATE('*', LEN(c.CustomerID)) AS CustomerIDStars,  

     REPLICATE(LEFT(c.CompanyName, 1), 5) AS CompanyNameInitialRepeated,  

     o.OrderID,  

     REPLICATE('0', 7 - LEN(CAST(o.OrderID AS VARCHAR))) + CAST(o.OrderID AS VARCHAR) AS OrderIDPaddedWithZeros  

 FROM  

     Customers c  

 LEFT JOIN  

     Orders o ON c.CustomerID = o.CustomerID  

 WHERE  

     LEN(c.CustomerID) > 3  

 ORDER BY  

     c.CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select().Top(15)  

     .Column("c.CustomerID", "CustomerID")  

     .Column("c.CompanyName", "CompanyName")  

     .Column(new REPLICATE("*", new LEN(new Column("c.CustomerID"))), "CustomerIDStars")  

     .Column(new REPLICATE(new LEFT(new Column("c.CompanyName"), 1), 5), "CompanyNameInitialRepeated")  

     .Column("o.OrderID", "OrderID")  

     .Column(  

         new REPLICATE("0", new ColumnArithmatic().Value(7).SUBTRACT().SqlFunction(new LEN(new CAST(new Column("o.OrderID"), SqlDataType.VARCHAR))))  

             .AddArithmatic(new Arithmatic().Add(new CAST(new Column("o.OrderID"), SqlDataType.VARCHAR)))  

     , "OrderIDPaddedWithZeros")  

     .From("Customers", "c")  

     .Join(new List<IJoin>()  

     {  

         new LEFTJOIN().TableName(new Table("Orders", "o"))  

             .On(new Column("c.CustomerID").Equale(new Column("o.CustomerID")))  

     })  

     .Where(new Where(new LEN(new Column("c.CustomerID")).GreaterThan(3)))  

     .OrderBy(new OrderBy().SetColumnAscending("c.CustomerID"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 15 c.CustomerID AS CustomerID,
              c.CompanyName AS CompanyName,
              REPLICATE(@pMAIN_2606220309000952220, LEN(c.CustomerID)) AS CustomerIDStars,
              REPLICATE(LEFT(c.CompanyName, @pMAIN_2606220309000952221), @pMAIN_2606220309000952222) AS CompanyNameInitialRepeated,
              o.OrderID AS OrderID,
              REPLICATE(@pMAIN_2606220309000952223, @pMAIN_2606220309000952224 - LEN(CAST (o.OrderID AS VARCHAR))) + CAST (o.OrderID AS VARCHAR) AS OrderIDPaddedWithZeros
FROM Customers AS c
     LEFT OUTER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
WHERE LEN(c.CustomerID) > @pMAIN_2606220309000952225
ORDER BY c.CustomerID ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of REPLICATE

SQL Server Query 2

            
 SELECT TOP 10   

 	od.OrderID, od.ProductID, od.Discount,   

 	CONCAT(REPLICATE('#', CAST(od.Discount * 20 AS INT)),   

 			REPLICATE('.', 20 - CAST(od.Discount * 20 AS INT))) AS DiscountProgressBar   

 FROM [Order Details] od    

 ORDER BY od.OrderID ASC,od.ProductID ASC  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select().Top(10)  

     .Columns("od.OrderID", "od.ProductID", "od.Discount")  

     .Column(new CONCAT(  

             new REPLICATE("#", new CAST(new ColumnArithmatic().Column("od.Discount").MULTIPLY().Value(20), SqlDataType.INT)),  

             new REPLICATE(".", new ColumnArithmatic().Value(20).SUBTRACT().SqlFunction(new CAST(new ColumnArithmatic().Column("od.Discount").MULTIPLY().Value(20), SqlDataType.INT)))  

         )  

     , "DiscountProgressBar")  

     .From("[Order Details]", "od")  

     .OrderBy(new OrderBy().SetColumnAscending("od.OrderID").SetColumnAscending("od.ProductID"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT TOP 10 od.OrderID,
              od.ProductID,
              od.Discount,
              CONCAT(REPLICATE(@pMAIN_2606220309001028600, CAST (od.Discount * @pMAIN_2606220309001028601 AS INT)), REPLICATE(@pMAIN_2606220309001028602, @pMAIN_2606220309001028603 - CAST (od.Discount * @pMAIN_2606220309001028604 AS INT))) AS DiscountProgressBar
FROM [Order Details] AS od
ORDER BY od.OrderID ASC, od.ProductID ASC;


            
        

Parameters (If used)

Query Results 2:

3. Usage of REPLICATE

SQL Server Query 3

            
 SELECT   

 	OrderID,  

 	OrderDate,  

 	CASE  

 		WHEN MONTH(OrderDate) BETWEEN 1 AND 3 THEN REPLICATE('-', 20)  

 		WHEN MONTH(OrderDate) BETWEEN 4 AND 6 THEN REPLICATE('=', 20)  

 		WHEN MONTH(OrderDate) BETWEEN 7 AND 9 THEN REPLICATE('+', 20)  

 		ELSE REPLICATE('*', 20)  

 	END AS MonthlySeparator  

 FROM  

 	Orders  

 WHERE OrderID BETWEEN 10316 AND 10319 OR OrderID BETWEEN 10398 AND 10401 OR  

 	  OrderID BETWEEN 10490 AND 10493 OR OrderID BETWEEN 10583 AND 10586 OR  

 	  OrderID BETWEEN 10686 AND 10689 OR OrderID BETWEEN 10806 AND 10809 OR  

 	  OrderID BETWEEN 10988 AND 10991   

 ORDER BY  

 	OrderDate;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select()  

     .Columns("OrderID", "OrderDate")  

     .Column(new CASE()  

         .When(new BETWEEN(new MONTH(new Column("OrderDate")), 1, 3))  

         .Then(new REPLICATE("-", 20))  

         .When(new BETWEEN(new MONTH(new Column("OrderDate")), 4, 6))  

         .Then(new REPLICATE("=", 20))  

         .When(new BETWEEN(new MONTH(new Column("OrderDate")), 7, 9))  

         .Then(new REPLICATE("+", 20))  

         .Else(new REPLICATE("*", 20))  

     , "MonthlySeparator")  

     .From("Orders")  

     .Where(new Where(new BETWEEN(new Column("OrderID"), 10316, 10319))  

         .OR(new BETWEEN(new Column("OrderID"), 10398, 10401))  

         .OR(new BETWEEN(new Column("OrderID"), 10490, 10493))  

         .OR(new BETWEEN(new Column("OrderID"), 10583, 10586))  

         .OR(new BETWEEN(new Column("OrderID"), 10686, 10689))  

         .OR(new BETWEEN(new Column("OrderID"), 10806, 10809))  

         .OR(new BETWEEN(new Column("OrderID"), 10988, 10991))  

     )  

     .OrderBy(new OrderBy().SetColumnAscending("OrderDate"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT OrderID,
       OrderDate,
       CASE WHEN MONTH(OrderDate) BETWEEN @pMAIN_2606220309001073370 AND @pMAIN_2606220309001073371 THEN REPLICATE(@pMAIN_2606220309001073372, @pMAIN_2606220309001073373) WHEN MONTH(OrderDate) BETWEEN @pMAIN_2606220309001073374 AND @pMAIN_2606220309001073375 THEN REPLICATE(@pMAIN_2606220309001073376, @pMAIN_2606220309001073377) WHEN MONTH(OrderDate) BETWEEN @pMAIN_2606220309001073378 AND @pMAIN_2606220309001073379 THEN REPLICATE(@pMAIN_260622030900107337_10, @pMAIN_260622030900107337_11) ELSE REPLICATE(@pMAIN_260622030900107337_12, @pMAIN_260622030900107337_13) END AS MonthlySeparator
FROM Orders
WHERE OrderID BETWEEN @pMAIN_260622030900107337_14 AND @pMAIN_260622030900107337_15
      OR OrderID BETWEEN @pMAIN_260622030900107337_16 AND @pMAIN_260622030900107337_17
      OR OrderID BETWEEN @pMAIN_260622030900107337_18 AND @pMAIN_260622030900107337_19
      OR OrderID BETWEEN @pMAIN_260622030900107337_20 AND @pMAIN_260622030900107337_21
      OR OrderID BETWEEN @pMAIN_260622030900107337_22 AND @pMAIN_260622030900107337_23
      OR OrderID BETWEEN @pMAIN_260622030900107337_24 AND @pMAIN_260622030900107337_25
      OR OrderID BETWEEN @pMAIN_260622030900107337_26 AND @pMAIN_260622030900107337_27
ORDER BY OrderDate ASC;


            
        

Parameters (If used)

Query Results 3: