CODEWITHSUNDEEP
sqlregexoracle-database
user1933888
user1933888

Reputation: 3017

simpler way to parse using regex

I have an input in the form of 'ABCD 3/1'. I need to parse the digit before '/', Also if the input does not match this pattern then return the original string itself.

I am using below query, which works, but there would be a way to this in single regex I believe, any hints appreciated.

select nvl(REGEXP_substr(REGEXP_substr('ABCD 3/1', '\d\/'), '\d'), 'ABCD 3/1') from dual;

Upvotes: 1

Views: 138

Answers (2)

Gary_W
Gary_W

Reputation: 10360

What about this? I believe it meets your requirements. Add more test cases as you see fit to the with clause.

SQL> with tbl(str) as (
     select 'ABCD 3/1'   from dual union
     select 'ABCD 332/1' from dual union
     select 'ABCD A/1'   from dual union
     select 'ABCD EFS'   from dual
   )
   select regexp_replace(str, '.*\s(\d)/\d.*', '\1') digit_before_slash
   from tbl;

DIGIT_BEFORE_SLASH
-----------------------------------------------------------------------------
3
ABCD 332/1
ABCD A/1
ABCD EFS

SQL>

Upvotes: 2

Aleksej
Aleksej

Reputation: 22949

You can try with REGEXP_REPLACE by mapping all your input string and picking only the part you want; for example, given this:

SQL> select regexp_replace('ABCD 3/1', '([A-Z]*)( )(\d)(\/)(\d)', '1:\1, 2:\2, 3:\3, 4:\4, 5:\5') from dual ;

REGEXP_REPLACE('ABCD3/1','
--------------------------
1:ABCD, 2: , 3:3, 4:/, 5:1

You can use '\3' to get only the third matched regexp:

SQL> select regexp_replace('ABCD 3/1', '([A-Z]*)( )(\d)(\/)(\d)', '\3') from dual ;

R
-
3

Upvotes: 2

Related Questions