CODEWITHSUNDEEP
sqlregexdatabaseoracle-database
jmq
jmq

Reputation: 10370

Number Wildcard in Where Clause

I'm trying to add a number wildcard to a query to look for a number in a specific position. The query looks something like this:

SELECT SUBMITTER
  FROM BASE_ELEMENT
 WHERE SUBMITTER LIKE 'm_%';

The problem with this query is that it picks up everything that starts with "m" and has a character in the second position. I need something that work like a Unix wildcard:

'm[0-9]*'

I want it to include m0, m1, m2, etc, but exclude ma, mb, mc, etc.

How would I accomplish this in Oracle 10g?

Upvotes: 1

Views: 13296

Answers (3)

Andomar
Andomar

Reputation: 238086

You could use regexp_like:

where regexp_like(submitter, '^m\d')

Upvotes: 1

bruno.zambiazi
bruno.zambiazi

Reputation: 1482

Try to use the REGEXP_LIKE function (http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm).

Your case could be solved like this:

select submitter from base_element where regexp_like( submitter, '^m[0-9]' );

Upvotes: 2

Ben
Ben

Reputation: 52863

In 10G you have the wonder of regular expressions. So, your query could be:

select submitter
  from base_element
 where regexp_like(submitter, '^m[[:digit:]]')

^ anchors the expression to the start of the line and [[:digit:]] matches to any digit.

Upvotes: 9

Related Questions