CODEWITHSUNDEEP
sqloracle-databaseregexp-replace
Georgy
Georgy

Reputation: 458

Get substring with REGEXP_SUBSTR

I need to use regexp_substr, but I can't use it properly

I have column (l.id) with numbers, for example: 

1234567891123!123  EXPECTED OUTPUT: 1234567891123
123456789112!123   EXPECTED OUTPUT: 123456789112
12345678911!123    EXPECTED OUTPUT: 12345678911
1234567891123!123  EXPECTED OUTPUT: 1234567891123

I want use regexp_substr before the exclamation mark (!)

SELECT REGEXP_SUBSTR(l.id,'[%!]',1,13)  from l.table

is it ok ?

Upvotes: 0

Views: 1472

Answers (5)

Gary_W
Gary_W

Reputation: 10360

Look at it like a delimited string where the bang is the delimiter and you want the first element, even if it is NULL. Make sure to test all possibilities, even the unexpected ones (ALWAYS expect the unexpected)! Here the assumption is if there is no delimiter you'll want what's there.

The regex returns the first element followed by a bang or the end of the line. Note this form of the regex handles a NULL first element.

SQL> with tbl(id, str) as (
      select 1, '1234567891123!123' from dual union all
      select 2, '123456789112!123' from dual union all
      select 3, '12345678911!123' from dual union all
      select 4, '1234567891123!123' from dual union all
      select 5, '!123' from dual union all
      select 6, '123!' from dual union all
      select 7, '' from dual union all
      select 8, '12345' from dual
   )
   select id, regexp_substr(str, '(.*?)(!|$)', 1, 1, NULL, 1)
   from tbl
   order by id;

        ID REGEXP_SUBSTR(STR
---------- -----------------
         1 1234567891123
         2 123456789112
         3 12345678911
         4 1234567891123
         5
         6 123
         7
         8 12345

8 rows selected.

SQL>

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

If you like to use REGEXP_SUBSTR rather than regexp_replace then you can use

SELECT REGEXP_SUBSTR(l.id,'^\d+')

assuming you have only numbers before !

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

If I understand correctly, this is the pattern that you want:

SELECT REGEXP_SUBSTR(l.id,'^[^!]+', 1) 
FROM (SELECT '1234567891123!123' as id from dual) l

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

You want to remove the exclamation mark and all following characters it seems. That is simply:

select regexp_replace(id, '!.*', '') from mytable;

Upvotes: 3

Fahmi
Fahmi

Reputation: 37473

You can try using INSTR() and substr()

DEMO

select substr(l.id,1,INSTR(l.id,'!', 1, 1)-1) from dual

Upvotes: 3

Related Questions