SQL Conditions
MS-SQL is very flexible in what can be used for the conditional statements. Some of the conditionals, such as equal, greater than, and less than, are obvious. Probably everyone knows how to select records using these. Statements such as:
SELECT FIELD1 from TABLEA where FIELD1=’B’
SELECT FIELD1 from TABLEA where FIELD1>’B’
SELECT FIELD1 from TABLEA where FIELD1<‘B’
have probably been used you many times.
SQL is capable of so much more, though. One that I find myself using repeatedly is the "like" statement. This is used where you need to match part of a string. The beauty of the "like" statement, is the ability to use a wildcard. The one that I use most often by far, is the "%" character. It matches any zero or more characters.
So, a statement such as:
SELECT FIELD1 from TABLEA where FIELD1 like ‘B%’
will result in all records where FIELD1 starts with "B". This would be where FIELD1 contains only the character "B", or those cases where FIELD1 starts with the character "B", and is followed by anything else.
Conversely, a statement such as:
SELECT FIELD1 from TABLEA where FIELD1 like ‘%B’
will result in all records where FIELD1 ends with "B". Again, if FIELD1 contains only the character "B", or those cases where FIELD1 starts with the character "B", and is preceded by anything else.
Finally, a statement such as:
SELECT FIELD1 from TABLEA where FIELD1 like ‘%B%’
will result in all records where FIELD1 contains the character "B" anywhere in the field.
Multiple characters, and wildcards, can be used to search for patterns. The statement:
SELECT FIELD1 from TABLEA where FIELD1 like ‘%B%B%’
will result in all records where FIELD1 contains two "B" characters. These can be anywhere in the field. Records would be returned if FIELD1 contains "Bob", "Hobby", or "Bob Barker". The only requirement is that there be two "B" characters, and whether they are upper or lower case is ignored.
Other wildcards for the "like" clause, include:
_: (underscore) Matches any single character
[]: Matches any single character within the specified range (such as [a-f]) or set (such as [abcdef]).
[^]: Matches any single character not within the specified range (such as [^a-f]) or set (such as [^abcdef]).
These can be combined to form very complex, and precise patters. For example, a statement such as:
SELECT FIELD1 from TABLEA where FIELD1 like ‘[a-c]_[^def]%B%1’
This will match records beginning with "A", "B", or "C" (the [a-c] wildcard. The second character can be anything (the "_" wildcard), and the third character can be anything other than "D", "E", and "F" (the "[^def]" wildcard). The first three characters can be followed by any zero or more characters (the "%" wildcard). Then, there must be a character "B" (again, either upper or lower case). The "B" can be followed by any zero or more characters (the second "%" wildcard). Finally, the field must end with the character "1" (the "1" in the last position). If any of these conditions fail, then the record would not be returned. For example if the third character is "D", then it will not be returned, because it fails the "[^def] wildcard for the third character.