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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close