Underscore: The Other SQL Wildcard Used For Single Characters
Read Time:1 Min, 17 Sec
Well, somewhere in the many years that I have been working with and writing SQL statements I missed something very useful that can be used as a wildcard for a single character: the underscore (_).
Yep, that’s right. The underscore can be used in a “where like” clause as a wildcard for a single character.
It works like so:
SELECT * FROM OL910.F9860 WHERE SIOBNM LIKE '_55%'
Ranges can also be specified in a “where like” clause.
| Wildcard character | Description | Example |
|---|---|---|
| % | Any string of zero or more characters. | WHERE title LIKE ‘%computer%’ finds all book titles with the word ‘computer’ anywhere in the book title. |
| _ (underscore) | Any single character. | WHERE au_fname LIKE ‘_ean’ finds all four-letter first names that end with ean (Dean, Sean, and so on). |
| [ ] | Any single character within the specified range ([a-f]) or set ([abcdef]). | WHERE au_lname LIKE ‘[C-P]arsen’ finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation. |
| [^] | Any single character not within the specified range ([^a-f]) or set ([^abcdef]). | WHERE au_lname LIKE ‘de[^l]%’ all author last names starting with de and where the following letter is not l. |
source: msdn
UPDATE: 5-28-2015
What if our “where clause” string contains an underscore?
SELECT * FROM TABLE WHERE COLUMN LIKE 'some[_]string%'
Average Rating