T-SQL example of Case-Sensitive Soundex

What?
This article is for demonstrating how to use a SOUNDEX in a select and then listing all the variations based on case-sensitivity.

Why?
We have a database with data in it. For a particular column we have setup default values, let's use the example "Data Not Yet Available". Unfortunately the end-user reported these default values sometimes list twice, especially when the case is different, eg. "Data not yet available". The final system (qlikview) was case-sensitive despite our server collation being case-insensitive.

Furthermore, we now have the task of finding all the variations of the default values which we found we could do with the built-in SOUNDEX function.

How?
Lets assume the following, we have a table containing:
copyraw
StudentID          StudentDetail           Soundex
-----------------  ----------------------  -----------
1                  Data not yet available  D300
2                  Data Not Yet Available  D300
3                  data not yet available  D300
4                  Daat oNt EYt Avialable  D300
  1.  StudentID          StudentDetail           Soundex 
  2.  -----------------  ----------------------  ----------- 
  3.  1                  Data not yet available  D300 
  4.  2                  Data Not Yet Available  D300 
  5.  3                  data not yet available  D300 
  6.  4                  Daat oNt EYt Avialable  D300 
This is obviously a simplified table as it doesn't tell us much but it is just for demo purposes. Consider the following query:
copyraw
SELECT DISTINCT 
	StudentDetail 
FROM 
	StudentTable 
WHERE 
	SOUNDEX(StudentDetail)=SOUNDEX('Data Not Yet Available')


-- yields

StudentDetail
-----------------
Data Not Yet Available
  1.  SELECT DISTINCT 
  2.      StudentDetail 
  3.  FROM 
  4.      StudentTable 
  5.  WHERE 
  6.      SOUNDEX(StudentDetail)=SOUNDEX('Data Not Yet Available') 
  7.   
  8.   
  9.  -- yields 
  10.   
  11.  StudentDetail 
  12.  ----------------- 
  13.  Data Not Yet Available 
Which isn't at all what we set out to achieve other than it identified the remaining values as having the same SOUNDEX value. Adding the COLLATE option straight after the column that needs to be case-sensitive returned the correct results:
copyraw
SELECT DISTINCT 
	StudentDetail COLLATE Latin1_General_CS_AS 
FROM 
	StudentTable 
WHERE 
	SOUNDEX(StudentDetail)=SOUNDEX('Data Not Yet Available')


-- yields

StudentDetail
-----------------
Data not yet available
Data Not Yet Available
data not yet available
Daat oNt EYt Avialable
  1.  SELECT DISTINCT 
  2.      StudentDetail COLLATE Latin1_General_CS_AS 
  3.  FROM 
  4.      StudentTable 
  5.  WHERE 
  6.      SOUNDEX(StudentDetail)=SOUNDEX('Data Not Yet Available') 
  7.   
  8.   
  9.  -- yields 
  10.   
  11.  StudentDetail 
  12.  ----------------- 
  13.  Data not yet available 
  14.  Data Not Yet Available 
  15.  data not yet available 
  16.  Daat oNt EYt Avialable 
Success!
Now we can run this on a column in a table to determine if there are discrepancies in our default values.

Additional
Note how this also picked up typos in the 4th value which is good as I didn't want to overcomplicate things using LEVENSHTEIN and DIFFERENCE:
copyraw
SOUNDEX('Data Not Yet Available') = SOUNDEX('Daat oNt EYt Avialable')
  1.  SOUNDEX('Data Not Yet Available') = SOUNDEX('Daat oNt EYt Avialable') 
Category: Transact-SQL :: Article: 438

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: The information on this website is provided without warranty and any content is merely the opinion of the author. Please try to test in development environments prior to adapting them to your production environments. The articles are written in good faith and, at the time of print, are working examples used in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin - Valid till 8 May 2022 bc1qjtp4l4ra452wzvuk9a45yfj82zkahsyy2z379y
© 2022 Joel Lipman .com. All Rights Reserved.