Other REVERSE SQL function

1. Usage of REVERSE to reverse the company name

SQL Server Query 1

            
 SELECT  

 	c.CustomerID,  

 	c.CompanyName,  

 	REVERSE(c.CompanyName) AS ReversedCompanyName,	  

 	SUBSTRING(c.CompanyName, 1, 1) + REPLICATE('*', LEN(c.CompanyName) - 2) +  

 	CASE WHEN LEN(c.CompanyName) > 1 THEN REVERSE(SUBSTRING(REVERSE(c.CompanyName), 1, 1)) ELSE '' END AS FirstLastReversedMasked,  

 	o.OrderID,  

 	REVERSE(CAST(o.OrderID AS VARCHAR)) AS ReversedOrderID  

 FROM  

 	Customers c  

 LEFT JOIN  

 	Orders o ON c.CustomerID = o.CustomerID  

 WHERE  

 	LEN(c.CompanyName) > 3 AND (o.OrderID BETWEEN 10316 AND 10319 OR o.OrderID BETWEEN 10398 AND 10401 OR  

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

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

 				  o.OrderID BETWEEN 10988 AND 10991)  

 ORDER BY  

 	c.CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .Select()  

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

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

     .Column(new REVERSE(new Column("c.CompanyName")), "ReversedCompanyName")  

     .Column(new CONCAT(  

             new SUBSTRING(new Column("c.CompanyName"), 1, 1),  

             new REPLICATE("*", new LEN(new Column("c.CompanyName")).AddArithmatic(new Arithmatic().Subtract(2))),  

             new CASE()  

                 .When(new LEN(new Column("c.CompanyName")).GreaterThan(1))  

                 .Then(new REVERSE(new SUBSTRING(new REVERSE(new Column("c.CompanyName")), 1, 1)))  

                 .Else("")  

         ), "FirstLastReversedMasked")  

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

     .Column(new REVERSE(new CAST(new Column("o.OrderID"), SqlDataType.VARCHAR)), "ReversedOrderID")  

     .From("Customers", "c")  

     .Join(new List<IJoin>()  

     {  

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

             .On(new Column("c.CustomerID"), SQLComparisonOperators.EQUALE_TO, new Column("o.CustomerID"))  

     })  

     .Where(new Where(new LEN(new Column("c.CompanyName")), SQLComparisonOperators.GREATER_THAN, 3)  

         .AND().StartBracket().Compare(new BETWEEN(new Column("o.OrderID"), "10316", "10319"))  

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

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

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

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

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

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

             .EndBracket()  

     )  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT c.CustomerID AS CustomerID,
       c.CompanyName AS CompanyName,
       REVERSE(c.CompanyName) AS ReversedCompanyName,
       CONCAT(SUBSTRING(c.CompanyName, @pMAIN_2507200206539603790, @pMAIN_2507200206539603791), REPLICATE(@pMAIN_2507200206539603792, LEN(c.CompanyName) - @pMAIN_2507200206539603793), CASE WHEN LEN(c.CompanyName) > @pMAIN_2507200206539603794 THEN REVERSE(SUBSTRING(REVERSE(c.CompanyName), @pMAIN_2507200206539603795, @pMAIN_2507200206539603796)) ELSE @pMAIN_2507200206539603797 END) AS FirstLastReversedMasked,
       o.OrderID AS OrderID,
       REVERSE(CAST (o.OrderID AS VARCHAR)) AS ReversedOrderID
FROM Customers AS c
     LEFT OUTER JOIN
     Orders AS o
     ON c.CustomerID = o.CustomerID
WHERE LEN(c.CompanyName) > @pMAIN_2507200206539603798
      AND (o.OrderID BETWEEN @pMAIN_2507200206539603799 AND @pMAIN_250720020653960379_10
           OR o.OrderID BETWEEN @pMAIN_250720020653960379_11 AND @pMAIN_250720020653960379_12
           OR o.OrderID BETWEEN @pMAIN_250720020653960379_13 AND @pMAIN_250720020653960379_14
           OR o.OrderID BETWEEN @pMAIN_250720020653960379_15 AND @pMAIN_250720020653960379_16
           OR o.OrderID BETWEEN @pMAIN_250720020653960379_17 AND @pMAIN_250720020653960379_18
           OR o.OrderID BETWEEN @pMAIN_250720020653960379_19 AND @pMAIN_250720020653960379_20
           OR o.OrderID BETWEEN @pMAIN_250720020653960379_21 AND @pMAIN_250720020653960379_22)
ORDER BY c.CustomerID ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of REVERSE in different fashions

SQL Server Query 2

            
 WITH CustomerNameParts AS (  

     SELECT  

         CustomerID,  

         CompanyName,  

         -- Split CompanyName into words using spaces as delimiters  

         TRIM(c1.value) AS Word,  

         ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY (SELECT NULL)) AS WordNumber  

     FROM  

         Customers  

     CROSS APPLY  

         STRING_SPLIT(CompanyName, ' ') AS c1  

 ),  

 ReversedWords AS (  

     SELECT  

         CustomerID,  

         CompanyName,  

         WordNumber,  

         Word,  

         REVERSE(Word) AS ReversedWord  

     FROM  

         CustomerNameParts  

 )  

 SELECT  

     c.CustomerID,  

     c.CompanyName,  

     -- Reverse the entire CompanyName  

     REVERSE(c.CompanyName) AS ReversedFullName,  

     -- Reverse each individual word in the CompanyName  

     STRING_AGG(rw.ReversedWord, ' ') WITHIN GROUP (ORDER BY rw.WordNumber) AS ReversedEachWord,  

     -- Display the first word reversed and the last word reversed  

     FIRST_VALUE(rw.ReversedWord) OVER (PARTITION BY rw.CustomerID ORDER BY rw.WordNumber ASC) AS FirstWordReversed,  

     LAST_VALUE(rw.ReversedWord) OVER (PARTITION BY rw.CustomerID ORDER BY rw.WordNumber ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastWordReversed  

 FROM  

     Customers c  

 JOIN  

     ReversedWords rw ON c.CustomerID = rw.CustomerID  

 GROUP BY  

     c.CustomerID, c.CompanyName, rw.CustomerID, rw.WordNumber, rw.ReversedWord  

 ORDER BY  

     c.CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .WithCTETable(new Table("CustomerNameParts"),  

         new SqlQueryBuilder().Select()  

             .Columns("CustomerID", "CompanyName")  

             .Column(new TRIM(new Column("c1.value")), "Word")  

             .Column(new ROW_NUMBER().PARTITION_BY(new Column("CustomerID")).ORDER_BY(new OrderBy().SetSelectNull()), "WordNumber")  

         .From("Customers")  

         .CrossApply(new STRING_SPLIT(new Column("CompanyName"), " "), "c1"))  

     .WithCTETable(new Table("ReversedWords"),  

         new SqlQueryBuilder().Select()  

             .Columns("CustomerID", "CompanyName", "WordNumber", "Word")  

             .Column(new REVERSE(new Column("Word")), "ReversedWord")  

             .From("CustomerNameParts")  

     )  

     .Select()  

     .Columns("c.CustomerID", "c.CompanyName")  

     .Column(new REVERSE(new Column("c.CompanyName")), "ReversedFullName")  

     .Column(new STRING_AGG(new Column("rw.ReversedWord"), " ").WITHIN_GROUP(new OrderBy().SetColumnAscending("rw.WordNumber")), "ReversedEachWord")  

     .Column(new FIRST_VALUE(new Column("rw.ReversedWord")).PARTITION_BY(new Column("rw.CustomerID"))  

         .ORDER_BY(new OrderBy().SetColumnAscending("rw.WordNumber")), "FirstWordReversed")  

     .Column(new LAST_VALUE(new Column("rw.ReversedWord")).PARTITION_BY(new Column("rw.CustomerID"))  

         .ORDER_BY(new OrderBy().SetWithUnbound("rw.WordNumber")), "LastWordReversed")  

     .From("Customers", "c")  

     .Join(new List<IJoin>  

     {  

         new INNERJOIN().TableName(new Table("ReversedWords","rw"))  

             .On(new Column("c.CustomerID"), SQLComparisonOperators.EQUALE_TO, new Column("rw.CustomerID"))  

     })  

     .GroupBy(new GroupBy("c.CustomerID", "c.CompanyName", "rw.CustomerID", "rw.WordNumber", "rw.ReversedWord"))  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
WITH  CustomerNameParts  AS  ( SELECT CustomerID, CompanyName, TRIM(c1.value) AS Word, ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY (SELECT NULL) ) AS WordNumber FROM Customers  CROSS APPLY STRING_SPLIT(CompanyName, @pMAIN_2507200206539804920) AS c1 )  ,   ReversedWords  AS  ( SELECT CustomerID, CompanyName, WordNumber, Word, REVERSE(Word) AS ReversedWord FROM CustomerNameParts  )  SELECT c.CustomerID, c.CompanyName, REVERSE(c.CompanyName) AS ReversedFullName, STRING_AGG(rw.ReversedWord, @pMAIN_2507200206539804921) WITHIN GROUP (ORDER BY rw.WordNumber ASC ) AS ReversedEachWord, FIRST_VALUE(rw.ReversedWord) OVER(PARTITION BY rw.CustomerID ORDER BY rw.WordNumber ASC ) AS FirstWordReversed, LAST_VALUE(rw.ReversedWord) OVER(PARTITION BY rw.CustomerID ORDER BY rw.WordNumber ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS LastWordReversed FROM Customers c  INNER JOIN ReversedWords rw  ON c.CustomerID = rw.CustomerID  GROUP BY c.CustomerID,c.CompanyName,rw.CustomerID,rw.WordNumber,rw.ReversedWord ORDER BY c.CustomerID ASC 
            
        

Parameters (If used)

Query Results 2: