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!
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
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.
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).
'_dis_'.