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_2507200210130149870, LEN(c.CustomerID)) AS CustomerIDStars,
              REPLICATE(LEFT(c.CompanyName, @pMAIN_2507200210130149871), @pMAIN_2507200210130149872) AS CompanyNameInitialRepeated,
              o.OrderID AS OrderID,
              REPLICATE(@pMAIN_2507200210130149873, @pMAIN_2507200210130149874 - 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_2507200210130149875
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_2507200210130218730, CAST (od.Discount * @pMAIN_2507200210130218731 AS INT)), REPLICATE(@pMAIN_2507200210130218732, @pMAIN_2507200210130218733 - CAST (od.Discount * @pMAIN_2507200210130218734 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_2507200210130254430 AND @pMAIN_2507200210130254431 THEN REPLICATE(@pMAIN_2507200210130254432, @pMAIN_2507200210130254433) WHEN MONTH(OrderDate) BETWEEN @pMAIN_2507200210130254434 AND @pMAIN_2507200210130254435 THEN REPLICATE(@pMAIN_2507200210130254436, @pMAIN_2507200210130254437) WHEN MONTH(OrderDate) BETWEEN @pMAIN_2507200210130254438 AND @pMAIN_2507200210130254439 THEN REPLICATE(@pMAIN_250720021013025443_10, @pMAIN_250720021013025443_11) ELSE REPLICATE(@pMAIN_250720021013025443_12, @pMAIN_250720021013025443_13) END AS MonthlySeparator
FROM Orders
WHERE OrderID BETWEEN @pMAIN_250720021013025443_14 AND @pMAIN_250720021013025443_15
      OR OrderID BETWEEN @pMAIN_250720021013025443_16 AND @pMAIN_250720021013025443_17
      OR OrderID BETWEEN @pMAIN_250720021013025443_18 AND @pMAIN_250720021013025443_19
      OR OrderID BETWEEN @pMAIN_250720021013025443_20 AND @pMAIN_250720021013025443_21
      OR OrderID BETWEEN @pMAIN_250720021013025443_22 AND @pMAIN_250720021013025443_23
      OR OrderID BETWEEN @pMAIN_250720021013025443_24 AND @pMAIN_250720021013025443_25
      OR OrderID BETWEEN @pMAIN_250720021013025443_26 AND @pMAIN_250720021013025443_27
ORDER BY OrderDate ASC;


            
        

Parameters (If used)

Query Results 3: