Q

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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close