String Comparison
When doing searches on string data there are a number of comparison operators that are popular. I’ll give you a few from SQL Server.
The = operation is the most common where you wish to strings to be exactly the same. Depending on your database and/or table configuration the = comparison will or will not be case sensitive.
SELECT text FROM syscomments WHERE text = ‘Nice’
Of course this wouldn’t return anything since there is no valid object that may be created with the word Nice.
The LIKE operation allows you to introduce wild cards in the comparison. When comparing a value in a table, if the comparison begins with literal characters then the query can take advantage of indexes on a searched column for better performance. A common wild card is % meaning substitute with any one or more characters.
SELECT text from syscomments WHERE text LIKE ‘%Nice%’
This query would return the text of any object that had the text Nice anywhere in the comment.
IN is another common comparison where you may provide a list of values, one of which may be the same as the sought value.
SELECT text from syscomments where text IN (‘A’, ‘B’)
Of course this doesn’t return any data because there would not be an object defined with that single character.
There are a couple other operators that are less commonly used due to performance, need or familiarity.
SOUNDEX is an operator that will convert a string into a numerical representation. According to MS SQL Books Online, the vowels are discarded, and the consonants evaluated on their phonic sound.
select SOUNDEX('BEN'), SOUNDEX ('BAIN'), SOUNDEX('BAN')Returns
B500 B500 B500
Here is a more common germanic spelling for the same name…
SELECT SOUNDEX('Snyder'), SOUNDEX ('Schneider'), SOUNDEX('Schnyder')Returns
S536 S253 S253
Notice this combination doesn’t have the close correlation
The final comparison I wanted to talk about today uses the operator DIFFERENCE. Difference is a function taking two strings as input and returning a numerical ranking of the comparison result. It ranks from 0 to 4 with 4 being the strongest match.
Select DIFFERNCE(‘Snyder’, ‘Schneider’) Returns
3
Many of these operations I’m sure you already knew. I hope some of them are intriguing. Help for implementing each of the operations may be found in the Books Online. I wonder if anyone has already written a Dot Net CRL function implementing Regular Expressions…?
You can send me a note if you find this helpful, or have other comparison operators you like to use. Direct your Email to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured Article(s)
What DBAs Need to Know about SQL Server 2012 High Availability and Disaster Recovery
Formerly known as SQL Server Denali, SQL Server 2012 brings with it a host of changes, particularly in the High Availability (HA) and Disaster Recovery (DR) space.
Featured White Paper(s)
All-At-Once Operations
Written by Itzik Ben-Gan with SolidQ
SQL supports a concept called all-at-onc… (read more)
Featured Script
Auto CRUD Procedure Generator Script
Auto CRUD Procedure Generator Script… (read more)