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_2602032249306587560
OR CompanyName LIKE @pMAIN_2602032249306587561
OR CompanyName LIKE @pMAIN_2602032249306587562
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_2602032249306587563 ELSE @pMAIN_2602032249306587564 END AS SimilarityStatus
FROM SoundexComparisonWithMisspellings AS scm
ORDER BY scm.CompanyName ASC;
Parameters (If used)
| Name |
Value |
| @pMAIN_2602032249306587560 |
% % |
| @pMAIN_2602032249306587561 |
A% |
| @pMAIN_2602032249306587562 |
B% |
| @pMAIN_2602032249306587563 |
Possible Misspelling/Similar Sound |
| @pMAIN_2602032249306587564 |
No Obvious Similar Sound Found |
Query Results 1:
| |
CustomerID |
CompanyName |
CompanyNameSoundex |
PotentialMisspelling |
SimilarityStatus |
| 1 |
ANTON
|
Antonio Moreno Taquería
|
A535
|
|
No Obvious Similar Sound Found
|
| 2 |
BERGS
|
Berglunds snabbköp
|
B624
|
|
No Obvious Similar Sound Found
|
| 3 |
BOTTM
|
Bottom-Dollar Markets
|
B350
|
|
No Obvious Similar Sound Found
|
| 4 |
CACTU
|
Cactus Comidas para llevar
|
C232
|
|
No Obvious Similar Sound Found
|
| 5 |
DRACD
|
Drachenblut Delikatessen
|
D625
|
|
No Obvious Similar Sound Found
|
| 6 |
EASTC
|
Eastern Connection
|
E236
|
|
No Obvious Similar Sound Found
|
| 7 |
FURIB
|
Furia Bacalhau e Frutos do Mar
|
F600
|
|
No Obvious Similar Sound Found
|
| 8 |
GODOS
|
Godos Cocina Típica
|
G320
|
|
No Obvious Similar Sound Found
|
| 9 |
LONEP
|
Lonesome Pine Restaurant
|
L525
|
|
No Obvious Similar Sound Found
|
| 10 |
MAISD
|
Maison Dewey
|
M250
|
|
No Obvious Similar Sound Found
|
| 11 |
OLDWO
|
Old World Delicatessen
|
O430
|
|
No Obvious Similar Sound Found
|
| 12 |
PARIS
|
Paris spécialités
|
P620
|
|
No Obvious Similar Sound Found
|
| 13 |
PICCO
|
Piccolo und mehr
|
P240
|
|
No Obvious Similar Sound Found
|
| 14 |
QUEDE
|
Que Delícia
|
Q000
|
|
No Obvious Similar Sound Found
|
| 15 |
ROMEY
|
Romero y tomillo
|
R560
|
|
No Obvious Similar Sound Found
|
| 16 |
TOMSP
|
Toms Spezialitäten
|
T520
|
|
No Obvious Similar Sound Found
|
| 17 |
TORTU
|
Tortuga Restaurante
|
T632
|
|
No Obvious Similar Sound Found
|
| 18 |
TRAIH
|
Trail's Head Gourmet Provisioners
|
T640
|
|
No Obvious Similar Sound Found
|
| 19 |
VINET
|
Vins et alcools Chevalier
|
V520
|
|
No Obvious Similar Sound Found
|
| 20 |
WOLZA
|
Wolski Zajazd
|
W420
|
|
No Obvious Similar Sound Found
|