Other DIFFERENCE SQL function

1. Usage of DIFFERENCE to show Soundex Difference

SQL Server Query 1

            
 SELECT TOP 10 c1.CompanyName AS CompanyName1, c2.CompanyName AS CompanyName2,   

 	DIFFERENCE(c1.CompanyName, c2.CompanyName) AS SoundexDifference   

 FROM Customers c1 CROSS JOIN Customers c2   

 WHERE c1.CustomerID < c2.CustomerID /* Avoid comparing a company to itself twice */   

 ORDER BY SoundexDifference DESC, CompanyName1, CompanyName2;  

            
        

Create SQL query with SqlQueryBuilder 1

            
 var (sql, parameters) = new SqlQueryBuilder()  

     .Select().Top(10)  

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

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

     .Column(new DIFFERENCE(new Column("c1.CompanyName"), new Column("c2.CompanyName")), "SoundexDifference")  

     .From("Customers", "c1")  

     .Join(new List<IJoin>()  

     {  

         new CROSSJOIN().TableName(new Table("Customers", "c2"))  

     })  

     .Where(new Where(new Column("c1.CustomerID").LessThan(new Column("c2.CustomerID"))))  

     .OrderBy(new OrderBy().SetColumnDescending("SoundexDifference").SetColumnAscending("CompanyName1"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 1

            
SELECT TOP 10 c1.CompanyName AS CompanyName1,
              c2.CompanyName AS CompanyName2,
              DIFFERENCE(c1.CompanyName, c2.CompanyName) AS SoundexDifference
FROM Customers AS c1 CROSS JOIN Customers AS c2
WHERE c1.CustomerID < c2.CustomerID
ORDER BY SoundexDifference DESC, CompanyName1 ASC;


            
        

Parameters (If used)

Query Results 1:

2. Usage of DIFFERENCE to show name difference

SQL Server Query 2

            
 SELECT FirstName, LastName,   

 	DIFFERENCE(FirstName, LastName) AS NameDifference   

 FROM Employees   

 ORDER BY NameDifference DESC, FirstName, LastName;  

            
        

Create SQL query with SqlQueryBuilder 2

            
 var (sql2, parameters2) = new SqlQueryBuilder()  

     .Select()  

     .Column("FirstName", "FirstName")  

     .Column("LastName", "LastName")  

     .Column(new DIFFERENCE(new Column("FirstName"), new Column("LastName")), "NameDifference")  

     .From("Employees")  

     .OrderBy(new OrderBy().SetColumnDescending("NameDifference").SetColumnAscending("FirstName").SetColumnDescending("LastName"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 2

            
SELECT FirstName AS FirstName,
       LastName AS LastName,
       DIFFERENCE(FirstName, LastName) AS NameDifference
FROM Employees
ORDER BY NameDifference DESC, FirstName ASC, LastName DESC;


            
        

Parameters (If used)

Query Results 2:

3. Usage of DIFFERENCE to show Product Soundex Difference

SQL Server Query 3

            
 SELECT TOP 15 p1.ProductName AS ProductName1, p2.ProductName AS ProductName2,   

 	DIFFERENCE(p1.ProductName, p2.ProductName) AS ProductSoundexDifference   

 FROM Products p1   

 CROSS JOIN Products p2   

 WHERE p1.ProductID < p2.ProductID   

 ORDER BY ProductSoundexDifference DESC, ProductName1, ProductName2;  

            
        

Create SQL query with SqlQueryBuilder 3

            
 var (sql3, parameters3) = new SqlQueryBuilder()  

     .Select().Top(15)  

     .Column("p1.ProductName", "ProductName1")  

     .Column("p2.ProductName", "ProductName2")  

     .Column(new DIFFERENCE(new Column("p1.ProductName"), new Column("p2.ProductName")), "ProductSoundexDifference")  

     .From("Products", "p1")  

     .Join(new List<IJoin>()  

     {   

         new CROSSJOIN().TableName("Products", "p2")  

     })  

     .Where(new Where(new Column("p1.ProductID"), SQLComparisonOperators.LESS_THAN, new Column("p2.ProductID")))  

     .OrderBy(new OrderBy().SetColumnDescending("ProductSoundexDifference")  

         .SetColumnAscending("ProductName1")  

         .SetColumnAscending("ProductName2"))  

     .Build();  

            
        

Query build by SqlQueryBuilder 3

            
SELECT TOP 15 p1.ProductName AS ProductName1,
              p2.ProductName AS ProductName2,
              DIFFERENCE(p1.ProductName, p2.ProductName) AS ProductSoundexDifference
FROM Products AS p1 CROSS JOIN Products AS p2
WHERE p1.ProductID < p2.ProductID
ORDER BY ProductSoundexDifference DESC, ProductName1 ASC, ProductName2 ASC;


            
        

Parameters (If used)

Query Results 3:

4. Usage of DIFFERENCE to identify difference

SQL Server Query 4

            
 SELECT DIFFERENCE('Smith', 'Smyth') AS SmithSmyth,       /*-- Output: 4 (very similar)*/   

 	DIFFERENCE('Johnson', 'Johnsen') AS JohnsonJohnsen, /*-- Output: 3 (similar)*/   

 	DIFFERENCE('Apple', 'Ape') AS AppleApe,           /*-- Output: 3 (similar)*/   

 	DIFFERENCE('Cat', 'Dog') AS CatDog;               /*-- Output: 0 (not similar)*/  

            
        

Create SQL query with SqlQueryBuilder 4

            
 var (sql4, parameters4) = new SqlQueryBuilder()  

     .Select().Column(new DIFFERENCE("Smith", "Smyth"), "SmithSmyth")  

     .Column(new DIFFERENCE("Johnson", "Johnsen"), "JohnsonJohnsen")  

     .Column(new DIFFERENCE("Apple", "Ape"), "AppleApe")  

     .Column(new DIFFERENCE("Cat", "Dog"), "CatDog")  

     .Build();  

            
        

Query build by SqlQueryBuilder 4

            
SELECT DIFFERENCE(@pMAIN_2507200210130394980, @pMAIN_2507200210130394981) AS SmithSmyth,
       DIFFERENCE(@pMAIN_2507200210130394982, @pMAIN_2507200210130394983) AS JohnsonJohnsen,
       DIFFERENCE(@pMAIN_2507200210130394984, @pMAIN_2507200210130394985) AS AppleApe,
       DIFFERENCE(@pMAIN_2507200210130394986, @pMAIN_2507200210130394987) AS CatDog;


            
        

Parameters (If used)

Query Results 4: