Other RTRIM SQL function

1. Usage of RTRIM function to fine tune product description

SQL Server Query 1

            
 WITH ProductDescriptionCleanup AS (  

     SELECT  

         ProductID,  

         ProductName,  

         -- Remove trailing spaces from the ProductName  

         RTRIM(ProductName) AS CleanProductName,  

         -- Simulate a description with potential trailing characters  

         ProductName + '   Extra Spaces   .' AS ProductDescription,  

 		CategoryID  

     FROM  

         Products  

 ),  

 DescriptionAnalysis AS (  

     SELECT  

         ProductID,  

         CleanProductName,  

         ProductDescription,  

         -- Remove trailing spaces and periods from the simulated description  

         RTRIM(ProductDescription) AS CleanedDescription,  

         -- Check if the cleaned description ends with a specific word (case-insensitive)  

         CASE  

             WHEN ProductDescription LIKE '%Sauce   .' THEN 1 ELSE 0  

         END AS EndsWithSauceWithTrailingJunk,  

         CASE  

             WHEN RTRIM(RTRIM(LOWER(ProductDescription))) LIKE '%sauce' THEN 1 ELSE 0  

         END AS CleanedDescriptionEndsWithSauce  

     FROM  

         ProductDescriptionCleanup  

 ),  

 CategoryNameCleanup AS (  

     SELECT  

         CategoryID,  

         CategoryName,  

         -- Remove trailing spaces from CategoryName  

         RTRIM(CategoryName) AS CleanCategoryName,  

         -- Simulate CategoryName with trailing hyphens  

         CategoryName + '---' AS CategoryNameWithHyphens  

     FROM  

         Categories  

 )   

 SELECT  

     pdc.ProductID,  

     pdc.ProductName,  

     pdc.CleanProductName,  

     da.ProductDescription,  

     da.CleanedDescription,  

     da.EndsWithSauceWithTrailingJunk,  

     da.CleanedDescriptionEndsWithSauce,  

     cnc.CategoryName,  

     cncup.CleanCategoryName,  

     RTRIM(cncup.CategoryNameWithHyphens) AS CleanCategoryNameWithHyphens  

 FROM  

     ProductDescriptionCleanup pdc  

 JOIN  

 	CategoryNameCleanup cncup ON cncup.CategoryID = pdc.CategoryID  

 JOIN  

     DescriptionAnalysis da ON pdc.ProductID = da.ProductID  

 JOIN  

     Categories cnc ON SUBSTRING(pdc.ProductName, 1, 3) = SUBSTRING(cnc.CategoryName, 1, 3) -- Illustrative join based on partial name  

 ORDER BY  

     pdc.ProductID;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

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

         .Select()  

         .Columns("ProductID", "ProductName")  

         .Column(new RTRIM(new Column("ProductName")), "CleanProductName")  

         .Column(new CONCAT(new Column("ProductName"), "    Extra Spaces   ."), "ProductDescription")  

         .Column("CategoryID", "CategoryID")  

         .From("Products")  

     )  

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

         .Select()  

         .Columns("ProductID", "CleanProductName", "ProductDescription")  

         .Column(new RTRIM(new RTRIM(new Column("ProductDescription")), "."), "CleanedDescription")  

         .Column(new CASE()  

             .When(new LIKE(new Column("ProductDescription"), "%Sauce   .")).Then("1").Else("0")  

         , "EndsWithSauceWithTrailingJunk")  

         .Column(new CASE()  

             .When(new LIKE(new RTRIM(new RTRIM(new LOWER(new Column("ProductDescription")))), "%sauce"))  

                 .Then("1").Else("0")  

         , "CleanedDescriptionEndsWithSauce")  

         .From("ProductDescriptionCleanup")  

     )  

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

         .Select()  

         .Columns("CategoryID", "CategoryName")  

         .Column(new RTRIM(new Column("CategoryName")), "CleanCategoryName")  

         .Column(new CONCAT(new Column("CategoryName"), "---"), "CategoryNameWithHyphens")  

         .From("Categories")  

     )  

     .Select()  

     .Columns("pdc.ProductID", "pdc.ProductName", "pdc.CleanProductName", "da.ProductDescription", "da.CleanedDescription", "da.EndsWithSauceWithTrailingJunk", "da.CleanedDescriptionEndsWithSauce", "cnc.CategoryName", "cncup.CleanCategoryName")  

     .Column(new RTRIM(new Column("cncup.CategoryNameWithHyphens")), "CleanCategoryNameWithHyphens")  

     .From("ProductDescriptionCleanup", "pdc")  

     .Join(new List<IJoin>()  

     {  

         new INNERJOIN().TableName(new Table("CategoryNameCleanup","cncup"))  

             .On(new Column("cncup.CategoryID").Equale(new Column("pdc.CategoryID"))),  

         new INNERJOIN().TableName(new Table("DescriptionAnalysis","da"))  

             .On(new Column("pdc.ProductID").Equale(new Column("da.ProductID"))),  

         new INNERJOIN().TableName(new Table("Categories","cnc"))  

             .On(new SUBSTRING(new Column("pdc.ProductName"), 1, 3).Equale(new SUBSTRING(new Column("cnc.CategoryName"), 1, 3)))  

     })  

     .OrderBy(new OrderBy().Set(new Column("pdc.ProductID")))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH ProductDescriptionCleanup
AS (SELECT ProductID,
           ProductName,
           RTRIM(ProductName) AS CleanProductName,
           CONCAT(ProductName, @pMAIN_2507192059031532030) AS ProductDescription,
           CategoryID AS CategoryID
    FROM Products),
 DescriptionAnalysis
AS (SELECT ProductID,
           CleanProductName,
           ProductDescription,
           RTRIM(ProductDescription) AS CleanedDescription,
           CASE WHEN ProductDescription LIKE @pMAIN_2507192059031532031 THEN @pMAIN_2507192059031532032 ELSE @pMAIN_2507192059031532033 END AS EndsWithSauceWithTrailingJunk,
           CASE WHEN RTRIM(RTRIM(LOWER(ProductDescription))) LIKE @pMAIN_2507192059031532034 THEN @pMAIN_2507192059031532035 ELSE @pMAIN_2507192059031532036 END AS CleanedDescriptionEndsWithSauce
    FROM ProductDescriptionCleanup),
 CategoryNameCleanup
AS (SELECT CategoryID,
           CategoryName,
           RTRIM(CategoryName) AS CleanCategoryName,
           CONCAT(CategoryName, @pMAIN_2507192059031532037) AS CategoryNameWithHyphens
    FROM Categories)
SELECT pdc.ProductID,
       pdc.ProductName,
       pdc.CleanProductName,
       da.ProductDescription,
       da.CleanedDescription,
       da.EndsWithSauceWithTrailingJunk,
       da.CleanedDescriptionEndsWithSauce,
       cnc.CategoryName,
       cncup.CleanCategoryName,
       RTRIM(cncup.CategoryNameWithHyphens) AS CleanCategoryNameWithHyphens
FROM ProductDescriptionCleanup AS pdc
     INNER JOIN
     CategoryNameCleanup AS cncup
     ON cncup.CategoryID = pdc.CategoryID
     INNER JOIN
     DescriptionAnalysis AS da
     ON pdc.ProductID = da.ProductID
     INNER JOIN
     Categories AS cnc
     ON SUBSTRING(pdc.ProductName, @pMAIN_2507192059031532038, @pMAIN_2507192059031532039) = SUBSTRING(cnc.CategoryName, @pMAIN_250719205903153203_10, @pMAIN_250719205903153203_11)
ORDER BY pdc.ProductID ASC;


            
        

Parameters (If used)

Query Results 1: