I need to query a database for entries that match a pattern of any four characters followed by SDS followed by three numeric characters, but %SDS% gets me too many things. Is there a character that represents only one position instead of any number of positions? I want to try to narrow the search down.
Requires Free Membership to View
Yes, there is, and it is the underappreciated underscore. When used as a wildcard character, the underscore stands for exactly one character, whereas the percent sign wildcard means zero or more characters.
where searchcolumn like '____SDS%'
Ensuring that the next three characters after SDS are numeric is trickier, and will depend on the string-handling functions available in your particular database system.
In SQL Server, additional wildcard capabilities are available, so you can check the numerics too:
where searchcolumn like '____SDS[0-9][0-9][0-9]%'
Some databases support regular expressions, e.g.
where searchcolumn regexp '^.{4}SDS[[:digit:]]{3}'
More information is in your documentation under pattern matching or string functions.
This was first published in August 2006
Join the conversationComment
Share
Comments
Results
Contribute to the conversation