CODEWITHSUNDEEP
sqlstringoracle-database
SMORF
SMORF

Reputation: 499

Oracle SQL - find string pattern in string

I need to extract some text from a string, but only where the text matches a string pattern. The string pattern will consist of...

2 numbers, a forward slash and 6 numbers e.g. 12/123456

or

2 numbers, a forward slash, 6 numbers, a hyphen and 2 numbers e.g. 12/123456-12

I know how to use INSTR to find a specific string. Is it possible to find a string that matches a specific pattern?

Upvotes: 0

Views: 2592

Answers (3)

jhenderson2099
jhenderson2099

Reputation: 964

You'll need to use regexp_like to filter the results and regexp_substr to get the substring.

Here is roughly what it should look like:

select id, myValue, regexp_substr(myValue, '[0-9]{2}/[0-9]{6}') as myRegExMatch
from Foo
where regexp_like(myValue,'^([a-zA-Z0-9 ])*[0-9]{2}/[0-9]{6}([a-zA-Z0-9 ])*$')

with a link to a SQLFiddle that you can see in action and adjust to your taste.

The regexp_like provided in the sample above takes into consideration the alphanumerics and whitespace characters that may bound the number pattern.

Upvotes: 1

Dr Y Wit
Dr Y Wit

Reputation: 2020

regexp_like(col_name,'^\d{2}/\d{6}($|-\d{2}$)')

or

regexp_like(col_name,'^\d{2}/\d{6}(-\d{2})?$')

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Use regexp_like. 

where regexp_like(col_name,'\s[0-9]{2}\/[0-9]{6}(-[0-9]{2})?\s')
  • \s matches a space. Include them at the start and end of pattern.
  • [0-9]{2}\/[0-9]{6} matches 2 numerics, a forward slash and 6 numerics
  • (-[0-9]{2})? is optional for a hyphen and 2 numerics following the previous pattern.

Upvotes: 0

Related Questions