Q
Problem solve Get help with specific problems with your technologies, process and projects.

Pattern matching

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 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.

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 last published in August 2006

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close