CODEWITHSUNDEEP
regexoracle-database
Pavel
Pavel

Reputation: 271

How create a regular expression to query Oracle?

I have table in Oracle DB. For example tbl1. And 1 column. For Example col1. My col1 - column text type. I need select all rows where text is 0 and It occurs once in the text. And if text It contains >1 digits I do not need it. For example i need this rows:

0
text0
0text
text 0 text
text0 text

and I do not need this rows

only text
0 0 
00
10
3243455
0text 1

I think I need regex but I do not know how to use them.

I write 

select * from tbl1 t where regexp_like(t.col1,'[0]{1}')

but i get only rows where contains 0

Upvotes: 0

Views: 37

Answers (2)

pablomatico
pablomatico

Reputation: 2242

Based on the answer by Ilya Kogan, you could get the rows you want with the following regular expression:

WITH tbl1 AS (SELECT '0' col1 FROM dual
              UNION
              SELECT 'text0' col1 FROM dual
              UNION
              SELECT '0text' col1 FROM dual
              UNION
              SELECT 'text 0 text' col1 FROM dual
              UNION
              SELECT 'text0 text ' col1 FROM dual
              UNION
              SELECT 'only text' col1 FROM dual
              UNION
              SELECT '0 0' col1 FROM dual
              UNION
              SELECT '00' col1 FROM dual
              UNION
              SELECT '10' col1 FROM dual
              UNION
              SELECT '3243455' col1 FROM dual
              UNION
              SELECT '0text 1' col1 FROM dual)
SELECT COL1, REGEXP_SUBSTR(col1,'\A\D*0\D*\Z')
FROM tbl1
WHERE REGEXP_LIKE(col1,'\A\D*0\D*\Z')

Where:

  • \A is the beginning of the line.
  • \D is a non-digit character.
  • 0 is the character for the number 0.
  • \Z is the end of the line.

Upvotes: 1

escargot agile
escargot agile

Reputation: 22379

[^0-9]*[0-9][^0-9]*

This means: any non-digit any number of times, then a digit exactly once, and then any non-digit any number of times.

You might need to add ^ and $ to force it to match the entire string:

^[^0-9]*[0-9][^0-9]*$

Upvotes: 2

Related Questions