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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

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: