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_2606200323166725070, LEN(c.CustomerID)) AS CustomerIDStars,
              REPLICATE(LEFT(c.CompanyName, @pMAIN_2606200323166725071), @pMAIN_2606200323166725072) AS CompanyNameInitialRepeated,
              o.OrderID AS OrderID,
              REPLICATE(@pMAIN_2606200323166725073, @pMAIN_2606200323166725074 - 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_2606200323166725075
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_2606200323166841760, CAST (od.Discount * @pMAIN_2606200323166841761 AS INT)), REPLICATE(@pMAIN_2606200323166841762, @pMAIN_2606200323166841763 - CAST (od.Discount * @pMAIN_2606200323166841764 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_2606200323166901700 AND @pMAIN_2606200323166901701 THEN REPLICATE(@pMAIN_2606200323166901702, @pMAIN_2606200323166901703) WHEN MONTH(OrderDate) BETWEEN @pMAIN_2606200323166901704 AND @pMAIN_2606200323166901705 THEN REPLICATE(@pMAIN_2606200323166901706, @pMAIN_2606200323166901707) WHEN MONTH(OrderDate) BETWEEN @pMAIN_2606200323166901708 AND @pMAIN_2606200323166901709 THEN REPLICATE(@pMAIN_260620032316690170_10, @pMAIN_260620032316690170_11) ELSE REPLICATE(@pMAIN_260620032316690170_12, @pMAIN_260620032316690170_13) END AS MonthlySeparator
FROM Orders
WHERE OrderID BETWEEN @pMAIN_260620032316690170_14 AND @pMAIN_260620032316690170_15
      OR OrderID BETWEEN @pMAIN_260620032316690170_16 AND @pMAIN_260620032316690170_17
      OR OrderID BETWEEN @pMAIN_260620032316690170_18 AND @pMAIN_260620032316690170_19
      OR OrderID BETWEEN @pMAIN_260620032316690170_20 AND @pMAIN_260620032316690170_21
      OR OrderID BETWEEN @pMAIN_260620032316690170_22 AND @pMAIN_260620032316690170_23
      OR OrderID BETWEEN @pMAIN_260620032316690170_24 AND @pMAIN_260620032316690170_25
      OR OrderID BETWEEN @pMAIN_260620032316690170_26 AND @pMAIN_260620032316690170_27
ORDER BY OrderDate ASC;


            
        

Parameters (If used)

Query Results 3: