CODEWITHSUNDEEP
sqlregexoracle-databaseplsql
SummersKing
SummersKing

Reputation: 331

regexp match character before a specify string (not include)

REGEXP_SUBSTR(label) ,'.*_dis')

this is for sql;

my database is mysql 

select REGEXP_SUBSTR(label) ,'.*_dis') as dis ,
substr(label,length(label))-1) as num
from table

table.lable col's data:

1. a_b_dis_12
2. a_dis_13
3. c_d_dis_23
3. c_dis_22

i want to get the character before '_dis' and the numeric part use regexp 

1.a_b   12
2.a     13
3.c_d   23
4.c     22

thanks a lot!

Upvotes: 0

Views: 80

Answers (3)

Popeye
Popeye

Reputation: 35910

You can use regexp_substr as follows:

Select regexp_substr(your_column, '^(.*)_dis_[0-9]+$',1,1,null,1) as dis,
       Regexp_substr(your_column, '[0-9]+$') as num
  From your table

Upvotes: 1

GMB
GMB

Reputation: 222482

I would use regexp_replace() as follows:

select
    regexp_replace(label, '_dis_.*$', '') dis,
    regexp_replace(label, '^.*_dis_', '') num
from mytable

The first expression suppresses everything from '_dis_ (included) to the end of the string. The second expression removes everything from the beginning of the string until '_dis_' (included).

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269843

You can use regexp_replace():

select regexp_replace(col, '^(.*)_dis.*$', '\1'),
       regexp_replace(col, '^.*_dis_([0-9]+)', '\1')
from (select 'a_b_dis_12' as col from dual union all
      select 'a_dis_13' as col from dual union all
      select 'c_d_dis_23' as col from dual union all
      select 'c_dis_22' as col from dual
     ) t;

Here is a db<>fiddle.

Upvotes: 0

Related Questions