Daily Knowledge Drop
There are a number of SQL wildcard characters, apart from the commonly used (at least in my case) zero or more characters, % wildcard. These include:
_- representing a single character[]- representing any single character within the brackets^- representing any character not in the brackets-- representing any single character in the specified range
Table setup
We have a simple Word table, with one column, also called Word. The table is populated with 5 records:
flout
hit
hot
hut
shout
Usage
Single character
The _ is used to represent a single character.
The following will select any three letter words which start with an 'h' and end with a 't':
SELECT *
FROM Word
WHERE Word LIKE 'h_t'
The result:
hit
hot
hut
Single character array
The [] characters are used to represent any single character specified within the brackets.
The following will select any three letter words which start with an 'h', have an 'o' OR 'u' as the middle letter, and which end with a 't':
SELECT *
FROM Word
WHERE Word LIKE 'h[ou]t'
The result:
hot
hut
To reiterate, only a single character from within the brackets is matched. For example
SELECT *
FROM Word
WHERE Word LIKE 'sh[ou]t'
No rows will be returned, as shout is NOT a match. Only shot and shut would be matched.
Character array negation
The ^ character is used, in conjunction with the characters in the brackets [], as a negation.
The following will select any three letter words which end in 'out', but NOT preceded by an 'l':
SELECT *
FROM Word
WHERE Word LIKE '%[^l]out'
The result:
shout
The negation applies to all character in the brackets.
The following will select any three letter word, which starts with an 'h', does NOT contain 'o' or 'u' as the middle letter, and ends in 't'.
SELECT *
FROM Word
WHERE Word LIKE 'h[^ou]t'
The result:
hit
Character range
The - character is used in conjunction with the characters in the brackets [], as a range specifier.
The following will select any three letter words starting with 'h', has any character in the alphabet between, and including, 'o' and 'u' in the middle, and ending in 't':
SELECT *
FROM Word
WHERE Word LIKE 'h[o-u]t'
The result:
hot
hut
Multiple characters
A quick look at the more common % character, which is used to represent zero or more characters.
The following will select any words starting with zero or more of any characters, followed by an 'h', followed by zero or more of any characters, and finally ending with a 't':
SELECT *
FROM Word
WHERE Word LIKE '%h%t'
The result:
hit
hot
hut
shout
Notes
Personally, I've never had any specific need or requirement for any of these wildcards, apart from % - however, they all have their place. With the knowledge of their existence, one is at least equipped to determine if any can add any value with each specific use case or requirement.
References
Daily Drop 120: 20-07-2022
At the start of 2022 I set myself the goal of learning one new coding related piece of knowledge a day.
It could be anything - some.NET / C# functionality I wasn't aware of, a design practice, a cool new coding technique, or just something I find interesting. It could be something I knew at one point but had forgotten, or something completely new, which I may or may never actually use.
The Daily Drop is a record of these pieces of knowledge - writing about and summarizing them helps re-enforce the information for myself, as well as potentially helps others learn something new as well.