CODEWITHSUNDEEP
sqlregexoracle-databasesubstrregexp-replace
Ammar Vahora
Ammar Vahora

Reputation: 1

Using REGEXP in oracle

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

Upvotes: 0

Views: 257

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

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;

db<>fiddle here

Upvotes: 1

masterdb
masterdb

Reputation: 1

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;

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65313

You can use select regexp_substr with '[^-]+$' pattern as :

select regexp_substr('AR001-330110092522102','[^-]+$') as "Result String" from dual

where

  • [^-] points out the starting point except for the character(dash) within the list
  • $ Matches the end of a string.
  • Matches one or more occurrences.

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

Demo

Upvotes: 1

Related Questions