I have a column filled with this type of data
AR001-330110092522102
AR001-330335000041402
AR001-330410092999901
Problem is, I only want the numbers after the dash my desired output is
330110092432102
330335091341402
330410092015901
How do I use REGEXP in oracle to accomplish this
Column name is identifier and table name is NSUS
You can use select regexp_substr with '[^-]+$' pattern as :
select regexp_substr('AR001-330110092522102','[^-]+$') as "Result String" from dual
where
Another option would be using regexp_replace :
select regexp_replace('AR001-330110092522102','(.*)\-(.*)','\2') as "Result String" from t
Indeed, you can also use substr(), instr() and length() functions combination to extract those strings such as
with t(str) as
(select 'AR001-330110092522102' from dual)
select substr(str,instr(str,'-')+1,length(str)) as "Result String" from t
You can use a very basic regular expression: .*
This means "accept any characters" - you just need to tell it to start after the '-' in the string, the position of which you can get using the INSTR function. So you end up with:
SELECT REGEXP_SUBSTR(YOUR_COLUMN, '.*', INSTR(YOUR_COLUMN, '-')+1)
FROM YOUR_TABLE;
The regexp that you are looking for is: -([0-9])+$
This means that you are looking for in the end of the string a "-" and at least one number. The "$" represents in the end of the string.
Then to get your number use $1 (this is your capture group)
Use this to test: https://regex101.com/r/f9kwXV/17
EDIT: I'm not sure if this works this way in oracle. Example:
SELECT REGEXP_REPLACE ('AR001-330335000041402', '-([0-9])+$', '$1')
FROM dual;
