Sithara Jayachandran Sithara Jayachandran - 4 months ago 10
SQL Question

How to take sub-string or right for the following condition in sql

Contract_Id
has data like


abc_rev4,abc_rev1,xxxxx_rev2,yyyyyyyy_rev4 .


I would only need "rev" from the data.

Data always ends with single digit numeric.

So How would I extract rev with the help of
substring
or
right


PS: Position of "v" in rev is always the last but first character

MT0 MT0
Answer

This will extract all instances of rev from the string:

SELECT REGEXP_REPLACE(
         'abc_rev4,abc_rev1,xxxxx_rev2,yyyyyyyy_rev4',
         '[^,]+(rev)\d(,|$)',
         '\1\2'
       ) AS revs
FROM   DUAL

Output:

REVS
---------------
rev,rev,rev,rev

If you have each in a separate column then:

WITH table_name ( value ) AS (
  SELECT 'abc_rev4' FROM DUAL UNION ALL
  SELECT 'abc_rev1' FROM DUAL UNION ALL
  SELECT 'xxxxx_rev2' FROM DUAL UNION ALL
  SELECT 'yyyyyyyy_rev4' FROM DUAL
)
SELECT value,
       SUBSTR( value, -4, 3 ) AS rev
FROM   table_name;

Output:

VALUE         REV
------------- ---
abc_rev4      rev
abc_rev1      rev
xxxxx_rev2    rev
yyyyyyyy_rev4 rev
Comments