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_2609201713465599970, LEN(c.CustomerID)) AS CustomerIDStars,
              REPLICATE(LEFT(c.CompanyName, @pMAIN_2609201713465599971), @pMAIN_2609201713465599972) AS CompanyNameInitialRepeated,
              o.OrderID AS OrderID,
              REPLICATE(@pMAIN_2609201713465599973, @pMAIN_2609201713465599974 - 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_2609201713465599975
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_2609201713465707820, CAST (od.Discount * @pMAIN_2609201713465707821 AS INT)), REPLICATE(@pMAIN_2609201713465707822, @pMAIN_2609201713465707823 - CAST (od.Discount * @pMAIN_2609201713465707824 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_2609201713465771660 AND @pMAIN_2609201713465771661 THEN REPLICATE(@pMAIN_2609201713465771662, @pMAIN_2609201713465771663) WHEN MONTH(OrderDate) BETWEEN @pMAIN_2609201713465771664 AND @pMAIN_2609201713465771665 THEN REPLICATE(@pMAIN_2609201713465771666, @pMAIN_2609201713465771667) WHEN MONTH(OrderDate) BETWEEN @pMAIN_2609201713465771668 AND @pMAIN_2609201713465771669 THEN REPLICATE(@pMAIN_260920171346577166_10, @pMAIN_260920171346577166_11) ELSE REPLICATE(@pMAIN_260920171346577166_12, @pMAIN_260920171346577166_13) END AS MonthlySeparator
FROM Orders
WHERE OrderID BETWEEN @pMAIN_260920171346577166_14 AND @pMAIN_260920171346577166_15
      OR OrderID BETWEEN @pMAIN_260920171346577166_16 AND @pMAIN_260920171346577166_17
      OR OrderID BETWEEN @pMAIN_260920171346577166_18 AND @pMAIN_260920171346577166_19
      OR OrderID BETWEEN @pMAIN_260920171346577166_20 AND @pMAIN_260920171346577166_21
      OR OrderID BETWEEN @pMAIN_260920171346577166_22 AND @pMAIN_260920171346577166_23
      OR OrderID BETWEEN @pMAIN_260920171346577166_24 AND @pMAIN_260920171346577166_25
      OR OrderID BETWEEN @pMAIN_260920171346577166_26 AND @pMAIN_260920171346577166_27
ORDER BY OrderDate ASC;


            
        

Parameters (If used)

Query Results 3: