Other RIGHT SQL function

1. Usage of RIGHT function

SQL Server Query 1

            
 WITH CustomerPhoneSuffix AS (  

     SELECT  

         CustomerID,  

         CompanyName,  

         Phone,  

         -- Extract the last 4 digits of the phone number  

         RIGHT(Phone, 4) AS LastFourDigits  

     FROM  

         Customers  

     WHERE  

         LEN(Phone) >= 4  

 ),  

 CustomerWithFaxSuffix AS (  

     SELECT  

         CustomerID,  

         CompanyName,  

         Fax,  

         -- Extract the last 3 digits of the fax number if it exists  

         CASE  

             WHEN Fax IS NOT NULL AND LEN(Fax) >= 3 THEN RIGHT(Fax, 3)  

             ELSE NULL  

         END AS LastThreeFaxDigits  

     FROM  

         Customers  

 ),  

 OrderDetailsWithUnitPriceSuffix AS (  

     SELECT  

         od.OrderID,  

         p.ProductName,  

         od.UnitPrice,  

         -- Extract the last digit of the UnitPrice  

         RIGHT(CAST(FLOOR(od.UnitPrice) AS VARCHAR), 1) AS UnitPriceLastDigit  

     FROM  

         [Order Details] od  

     JOIN  

         Products p ON od.ProductID = p.ProductID  

 )  

 SELECT  

     c.CustomerID,  

     c.CompanyName,  

     cps.LastFourDigits AS PhoneLastFour,  

     cfx.LastThreeFaxDigits AS FaxLastThree,  

     oud.OrderID,  

     oud.ProductName,  

     oud.UnitPrice,  

     oud.UnitPriceLastDigit  

 FROM  

     Customers c  

 LEFT JOIN  

     CustomerPhoneSuffix cps ON c.CustomerID = cps.CustomerID  

 LEFT JOIN  

     CustomerWithFaxSuffix cfx ON c.CustomerID = cfx.CustomerID  

 LEFT JOIN  

     OrderDetailsWithUnitPriceSuffix oud ON c.CustomerID = SUBSTRING(CAST(oud.OrderID AS VARCHAR), 1, 5) -- Attempting a join based on partial OrderID (illustrative, might not be logically sound)  

 WHERE  

     cps.LastFourDigits IS NOT NULL OR cfx.LastThreeFaxDigits IS NOT NULL OR oud.UnitPriceLastDigit IS NOT NULL  

 ORDER BY  

     c.CustomerID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

     .WithCTETable(new Table("CustomerPhoneSuffix"), new SqlQueryBuilder()  

         .Select()  

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

         .Column(new RIGHT(new Column("Phone"), 4), "LastFourDigits")  

         .From("Customers")  

         .Where(new Where(new LEN(new Column("Phone")).GreaterThanOrEqualeTo(4)))  

     )  

     .WithCTETable(new Table("CustomerWithFaxSuffix"), new SqlQueryBuilder()  

         .Select()  

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

         .Column(new CASE()  

             .When(new IS_NOT_NULL(new Column("Fax")).AND(new LEN(new Column("Fax")).GreaterThanOrEqualeTo(3)))  

                 .Then(new RIGHT(new Column("Fax"), 3))  

             .Else(new Column().SetNull())  

         , "LastThreeFaxDigits")  

         .From("Customers")  

     )  

     .WithCTETable(new Table("OrderDetailsWithUnitPriceSuffix"), new SqlQueryBuilder()  

         .Select()  

         .Columns("od.OrderID", "p.ProductName", "od.UnitPrice")  

         .Column(new RIGHT(new CAST(new FLOOR(new Column("od.UnitPrice")), SqlDataType.VARBINARY), 1), "UnitPriceLastDigit")  

         .From("[Order Details]", "od")  

         .Join(new List<IJoin>() {  

             new INNERJOIN().TableName(new Table("Products","p"))  

                 .On(new Column("od.ProductID").Equale(new Column("p.ProductID")))  

         })  

     )  

     .Select()  

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

     .Column("cps.LastFourDigits", "PhoneLastFour")  

     .Column("cfx.LastThreeFaxDigits", "FaxLastThree")  

     .Columns("oud.OrderID", "oud.ProductName", "oud.UnitPrice", "oud.UnitPriceLastDigit")  

     .From("Customers", "c")  

     .Join(new List<IJoin>()  

     {  

         new LEFTJOIN().TableName(new Table("CustomerPhoneSuffix","cps"))  

             .On(new Column("c.CustomerID").Equale(new Column("cps.CustomerID"))),  

         new LEFTJOIN().TableName(new Table("CustomerWithFaxSuffix","cfx"))  

             .On(new Column("c.CustomerID").Equale(new Column("cfx.CustomerID"))),  

         new LEFTJOIN().TableName(new Table("OrderDetailsWithUnitPriceSuffix","oud"))  

             .On(new Column("c.CustomerID").Equale(new SUBSTRING(new CAST(new Column("oud.OrderID"), SqlDataType.VARCHAR),1,5)))  

     })  

     .Where(new Where(new IS_NOT_NULL(new Column("cps.LastFourDigits")))  

         .OR(new IS_NOT_NULL(new Column("cfx.LastThreeFaxDigits")))  

         .OR(new IS_NOT_NULL(new Column("oud.UnitPriceLastDigit")))  

     )  

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

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH CustomerPhoneSuffix
AS (SELECT CustomerID,
           CompanyName,
           Phone,
           RIGHT(Phone, @pMAIN_2507200210130173140) AS LastFourDigits
    FROM Customers
    WHERE LEN(Phone) >= @pMAIN_2507200210130173141),
 CustomerWithFaxSuffix
AS (SELECT CustomerID,
           CompanyName,
           Fax,
           CASE WHEN Fax IS NOT NULL
                     AND LEN(Fax) >= @pMAIN_2507200210130173142 THEN RIGHT(Fax, @pMAIN_2507200210130173143) ELSE NULL END AS LastThreeFaxDigits
    FROM Customers),
 OrderDetailsWithUnitPriceSuffix
AS (SELECT od.OrderID,
           p.ProductName,
           od.UnitPrice,
           RIGHT(CAST (FLOOR(od.UnitPrice) AS VARBINARY), @pMAIN_2507200210130173144) AS UnitPriceLastDigit
    FROM [Order Details] AS od
         INNER JOIN
         Products AS p
         ON od.ProductID = p.ProductID)
SELECT c.CustomerID,
       c.CompanyName,
       cps.LastFourDigits AS PhoneLastFour,
       cfx.LastThreeFaxDigits AS FaxLastThree,
       oud.OrderID,
       oud.ProductName,
       oud.UnitPrice,
       oud.UnitPriceLastDigit
FROM Customers AS c
     LEFT OUTER JOIN
     CustomerPhoneSuffix AS cps
     ON c.CustomerID = cps.CustomerID
     LEFT OUTER JOIN
     CustomerWithFaxSuffix AS cfx
     ON c.CustomerID = cfx.CustomerID
     LEFT OUTER JOIN
     OrderDetailsWithUnitPriceSuffix AS oud
     ON c.CustomerID = SUBSTRING(CAST (oud.OrderID AS VARCHAR), @pMAIN_2507200210130173145, @pMAIN_2507200210130173146)
WHERE cps.LastFourDigits IS NOT NULL
      OR cfx.LastThreeFaxDigits IS NOT NULL
      OR oud.UnitPriceLastDigit IS NOT NULL
ORDER BY c.CustomerID ASC;


            
        

Parameters (If used)

Query Results 1: