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_2605061913516022610, LEN(c.CustomerID)) AS CustomerIDStars,
              REPLICATE(LEFT(c.CompanyName, @pMAIN_2605061913516022611), @pMAIN_2605061913516022612) AS CompanyNameInitialRepeated,
              o.OrderID AS OrderID,
              REPLICATE(@pMAIN_2605061913516022613, @pMAIN_2605061913516022614 - 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_2605061913516022615
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_2605061913516126890, CAST (od.Discount * @pMAIN_2605061913516126891 AS INT)), REPLICATE(@pMAIN_2605061913516126892, @pMAIN_2605061913516126893 - CAST (od.Discount * @pMAIN_2605061913516126894 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_2605061913516174630 AND @pMAIN_2605061913516174631 THEN REPLICATE(@pMAIN_2605061913516174632, @pMAIN_2605061913516174633) WHEN MONTH(OrderDate) BETWEEN @pMAIN_2605061913516174634 AND @pMAIN_2605061913516174635 THEN REPLICATE(@pMAIN_2605061913516174636, @pMAIN_2605061913516174637) WHEN MONTH(OrderDate) BETWEEN @pMAIN_2605061913516174638 AND @pMAIN_2605061913516174639 THEN REPLICATE(@pMAIN_260506191351617463_10, @pMAIN_260506191351617463_11) ELSE REPLICATE(@pMAIN_260506191351617463_12, @pMAIN_260506191351617463_13) END AS MonthlySeparator
FROM Orders
WHERE OrderID BETWEEN @pMAIN_260506191351617463_14 AND @pMAIN_260506191351617463_15
      OR OrderID BETWEEN @pMAIN_260506191351617463_16 AND @pMAIN_260506191351617463_17
      OR OrderID BETWEEN @pMAIN_260506191351617463_18 AND @pMAIN_260506191351617463_19
      OR OrderID BETWEEN @pMAIN_260506191351617463_20 AND @pMAIN_260506191351617463_21
      OR OrderID BETWEEN @pMAIN_260506191351617463_22 AND @pMAIN_260506191351617463_23
      OR OrderID BETWEEN @pMAIN_260506191351617463_24 AND @pMAIN_260506191351617463_25
      OR OrderID BETWEEN @pMAIN_260506191351617463_26 AND @pMAIN_260506191351617463_27
ORDER BY OrderDate ASC;


            
        

Parameters (If used)

Query Results 3: