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_2608060433373290260
OR CompanyName LIKE @pMAIN_2608060433373290261
OR CompanyName LIKE @pMAIN_2608060433373290262
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_2608060433373290263 ELSE @pMAIN_2608060433373290264 END AS SimilarityStatus
FROM SoundexComparisonWithMisspellings AS scm
ORDER BY scm.CompanyName ASC;
Parameters (If used)
| Name |
Value |
| @pMAIN_2608060433373290260 |
% % |
| @pMAIN_2608060433373290261 |
A% |
| @pMAIN_2608060433373290262 |
B% |
| @pMAIN_2608060433373290263 |
Possible Misspelling/Similar Sound |
| @pMAIN_2608060433373290264 |
No Obvious Similar Sound Found |
Query Results 1:
| |
CustomerID |
CompanyName |
CompanyNameSoundex |
PotentialMisspelling |
SimilarityStatus |
| 1 |
BONAP
|
Bon app'
|
B500
|
|
No Obvious Similar Sound Found
|
| 2 |
CENTC
|
Centro comercial Moctezuma
|
C536
|
|
No Obvious Similar Sound Found
|
| 3 |
CHOPS
|
Chop-suey Chinese
|
C100
|
|
No Obvious Similar Sound Found
|
| 4 |
DRACD
|
Drachenblut Delikatessen
|
D625
|
|
No Obvious Similar Sound Found
|
| 5 |
LACOR
|
La corne d'abondance
|
L000
|
La maison d'Asie
|
Possible Misspelling/Similar Sound
|
| 6 |
LAZYK
|
Lazy K Kountry Store
|
L200
|
|
No Obvious Similar Sound Found
|
| 7 |
LEHMS
|
Lehmanns Marktstand
|
L552
|
|
No Obvious Similar Sound Found
|
| 8 |
MAGAA
|
Magazzini Alimentari Riuniti
|
M225
|
|
No Obvious Similar Sound Found
|
| 9 |
MEREP
|
Mère Paillarde
|
M600
|
|
No Obvious Similar Sound Found
|
| 10 |
OLDWO
|
Old World Delicatessen
|
O430
|
|
No Obvious Similar Sound Found
|
| 11 |
PICCO
|
Piccolo und mehr
|
P240
|
|
No Obvious Similar Sound Found
|
| 12 |
RATTC
|
Rattlesnake Canyon Grocery
|
R342
|
|
No Obvious Similar Sound Found
|
| 13 |
SEVES
|
Seven Seas Imports
|
S150
|
|
No Obvious Similar Sound Found
|
| 14 |
SPECD
|
Spécialités du monde
|
S124
|
|
No Obvious Similar Sound Found
|
| 15 |
THECR
|
The Cracker Box
|
T000
|
The Big Cheese
|
Possible Misspelling/Similar Sound
|
| 16 |
TOMSP
|
Toms Spezialitäten
|
T520
|
|
No Obvious Similar Sound Found
|
| 17 |
VINET
|
Vins et alcools Chevalier
|
V520
|
|
No Obvious Similar Sound Found
|
| 18 |
WARTH
|
Wartian Herkku
|
W635
|
|
No Obvious Similar Sound Found
|
| 19 |
WHITC
|
White Clover Markets
|
W300
|
|
No Obvious Similar Sound Found
|
| 20 |
WOLZA
|
Wolski Zajazd
|
W420
|
|
No Obvious Similar Sound Found
|