Other RTRIM SQL function

1. Usage of SOUNDEX to show similar or not similar or potential misspelling

SQL Server Query 1

            
 WITH CustomerSoundex AS (  

     SELECT  

         CustomerID,  

         CompanyName,  

         SOUNDEX(CompanyName) AS CompanyNameSoundex  

     FROM  

         Customers  

 ),  

 SimilarCompanyNames AS (  

     SELECT  

         cs1.CustomerID AS CustomerID1,  

         cs1.CompanyName AS CompanyName1,  

         cs2.CustomerID AS CustomerID2,  

         cs2.CompanyName AS CompanyName2,  

         cs1.CompanyNameSoundex  

     FROM  

         CustomerSoundex cs1  

     JOIN  

         CustomerSoundex cs2 ON cs1.CompanyNameSoundex = cs2.CompanyNameSoundex  

                                 AND cs1.CustomerID <> cs2.CustomerID  

     WHERE  

         cs1.CustomerID < cs2.CustomerID -- Avoid duplicate pairs  

 ),  

 CustomerWithPotentialMisspellings AS (  

     SELECT TOP 20 -- Limit for demonstration  

         CustomerID,  

         CompanyName  

     FROM  

         Customers  

     WHERE  

         CompanyName LIKE '% %' OR -- Companies with spaces  

         CompanyName LIKE 'A%' OR   -- Companies starting with 'A'  

         CompanyName LIKE 'B%'     -- Companies starting with 'B'  

     ORDER BY  

         NEWID() -- Random order for demonstration  

 ),  

 SoundexComparisonWithMisspellings AS (  

     SELECT  

         c.CustomerID,  

         c.CompanyName,  

         cs.CompanyNameSoundex,  

         (SELECT TOP 1 CompanyName FROM Customers WHERE SOUNDEX(CompanyName) = cs.CompanyNameSoundex AND CustomerID <> c.CustomerID ORDER BY NEWID()) AS PotentialMisspelling  

     FROM  

         CustomerWithPotentialMisspellings c  

     LEFT JOIN  

         CustomerSoundex cs ON c.CustomerID = cs.CustomerID  

 )  

 SELECT  

     scm.CustomerID,  

     scm.CompanyName,  

     scm.CompanyNameSoundex,  

     scm.PotentialMisspelling,  

     CASE  

         WHEN scm.PotentialMisspelling IS NOT NULL THEN 'Possible Misspelling/Similar Sound'  

         ELSE 'No Obvious Similar Sound Found'  

     END AS SimilarityStatus  

 FROM  

     SoundexComparisonWithMisspellings scm  

 ORDER BY  

     scm.CompanyName;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql1, parameters1) = new SqlQueryBuilder()  

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

         .Select()  

         .Columns("CustomerID", "CompanyName")  

         .Column(new SOUNDEX(new Column("CompanyName")), "CompanyNameSoundex")  

         .From("Customers")  

     )  

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

         .Select()  

         .Column("cs1.CustomerID", "CustomerID1")  

         .Column("cs1.CompanyName", "CompanyName1")  

         .Column("cs2.CustomerID", "CustomerID2")  

         .Column("cs2.CompanyName", "CompanyName2")  

         .Columns("cs1.CompanyNameSoundex")  

         .From("CustomerSoundex", "cs1")  

         .Join(new List<IJoin>()  

         {  

             new INNERJOIN().TableName(new Table("CustomerSoundex","cs2"))  

                 .On(new Column("cs1.CompanyNameSoundex").Equale(new Column("cs2.CompanyNameSoundex")))  

                     .AND(new Column("cs1.CustomerID").NotEqualeTo(new Column("cs2.CustomerID")))  

         })  

     )  

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

         .Select().Top(20)  

         .Columns("CustomerID", "CompanyName")  

         .From("Customers")  

         .Where(new Where(new LIKE(new Column("CompanyName"), "% %"))  

             .OR(new LIKE(new Column("CompanyName"), "A%"))  

             .OR(new LIKE(new Column("CompanyName"), "B%"))  

         )  

         .OrderBy(new OrderBy().Set(new NEWID()))  

     )  

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

         .Select()  

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

         .Column(new SqlQueryBuilder().Select().Top(1)  

             .Column("CompanyName", "CompanyName")  

             .From("Customers")  

             .Where(new Where(new SOUNDEX(new Column("CompanyName")).Equale(new Column("cs.CompanyNameSoundex")))  

                 .AND(new Column("CustomerID").NotEqualeTo(new Column("c.CustomerID")))  

             )  

             .OrderBy(new OrderBy().Set(new NEWID())), "PotentialMisspelling")  

         .From("CustomerWithPotentialMisspellings", "c")  

         .Join(new List<IJoin>()  

         {  

             new INNERJOIN().TableName(new Table("CustomerSoundex","cs"))  

                 .On(new Column("c.CustomerID").Equale(new Column("cs.CustomerID")))  

         })  

     )  

     .Select()  

     .Columns("scm.CustomerID", "scm.CompanyName", "scm.CompanyNameSoundex", "scm.PotentialMisspelling")  

     .Column(new CASE()  

         .When(new IS_NOT_NULL(new Column("scm.PotentialMisspelling"))).Then("Possible Misspelling/Similar Sound")  

         .Else("No Obvious Similar Sound Found"), "SimilarityStatus")  

     .From("SoundexComparisonWithMisspellings", "scm")  

     .OrderBy(new OrderBy().SetColumnAscending("scm.CompanyName"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
WITH CustomerSoundex
AS (SELECT CustomerID,
           CompanyName,
           SOUNDEX(CompanyName) AS CompanyNameSoundex
    FROM Customers),
 SimilarCompanyNames
AS (SELECT cs1.CustomerID AS CustomerID1,
           cs1.CompanyName AS CompanyName1,
           cs2.CustomerID AS CustomerID2,
           cs2.CompanyName AS CompanyName2,
           cs1.CompanyNameSoundex
    FROM CustomerSoundex AS cs1
         INNER JOIN
         CustomerSoundex AS cs2
         ON cs1.CompanyNameSoundex = cs2.CompanyNameSoundex
            AND cs1.CustomerID <> cs2.CustomerID),
 CustomerWithPotentialMisspellings
AS (SELECT TOP 20 CustomerID,
                  CompanyName
    FROM Customers
    WHERE CompanyName LIKE @pMAIN_2609201712509351250
          OR CompanyName LIKE @pMAIN_2609201712509351251
          OR CompanyName LIKE @pMAIN_2609201712509351252
    ORDER BY NEWID() ASC),
 SoundexComparisonWithMisspellings
AS (SELECT c.CustomerID,
           c.CompanyName,
           cs.CompanyNameSoundex,
           (SELECT TOP 1 CompanyName AS CompanyName
            FROM Customers
            WHERE SOUNDEX(CompanyName) = cs.CompanyNameSoundex
                  AND CustomerID <> c.CustomerID
            ORDER BY NEWID() ASC) AS PotentialMisspelling
    FROM CustomerWithPotentialMisspellings AS c
         INNER JOIN
         CustomerSoundex AS cs
         ON c.CustomerID = cs.CustomerID)
SELECT scm.CustomerID,
       scm.CompanyName,
       scm.CompanyNameSoundex,
       scm.PotentialMisspelling,
       CASE WHEN scm.PotentialMisspelling IS NOT NULL THEN @pMAIN_2609201712509351253 ELSE @pMAIN_2609201712509351254 END AS SimilarityStatus
FROM SoundexComparisonWithMisspellings AS scm
ORDER BY scm.CompanyName ASC;


            
        

Parameters (If used)

Query Results 1: