CODEWITHSUNDEEP
sqlregexoracle-database
san
san

Reputation: 197

Extracting a String Portion using Regular Expressions in Oracle

enter image description here

enter image description here

I have a requirement where I need to extract a specific portion from a column value.

Review: Project BAN27: Content BAN27S001: Deviation

Now in the above text, I need only BAN27S001 portion in a new column. The only common factor I have is a portion of String (like BAN27) is available in other column. Now I am trying to extract BAN27S001 using the column which has BAN27 as the value. Can we implement it in Oracle using Regular Expressions..Please suggest if there is any other way to achieve this.

Could you please help me in extracting the string.

Thanks San

Upvotes: 1

Views: 105

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

You might use [:alnum:] to detect alphanumeric portion as in the following sample :

with t(colA,colB) as
(
 select 'BAN27', 'Review: Project BAN27: Content BAN27S001: Deviation' from dual union all
 select 'BAN28', 'Review: Project BAN28: Content BAN28S002: Bio' from dual 
)
select colA,
       regexp_substr(colB,colA||'([[:alnum:]]+\.?)') as "Substring"
  from t;

COLA    Substring
-----   ---------
BAN27   BAN27S001
BAN28   BAN28S002

Rextester Demo 1

Edit : With respect to your last comment, if the last three rows to be considered, then you might use the following excerpt:

with t(colA,colB) as
(
 select '177', '177-130/ data continue to be collected' from dual union all
 select '177', '177-131 /log accordingly' from dual union all
 select '524', '524 - 23 & get these resolved' from dual    
)
select colA, nvl( regexp_substr(colB,colA||'(\-[[:digit:]]+)'),
                  regexp_substr(colB,colA||'([[:space:]]\-[[:space:]][[:digit:]]+)')) "Substring"
  from t;

COLA    Substring
-----   ---------
177     177-130
177     177-131
524     524 - 23

Rextester Demo 2

Upvotes: 1

Ajay Venkata Raju
Ajay Venkata Raju

Reputation: 1168

this query would identify the colb data which is like ColA+'S'

SELECT 
      REGEXP_SUBSTR (colb, '[^ ]+', 1,
      (case  
       when REGEXP_SUBSTR(colb, '[^ ]+', 1, 1) like '%'|| cola||'S%' then 1 
       when REGEXP_SUBSTR(colb, '[^ ]+', 1, 2) like '%'|| cola||'S%' then 2 
      when REGEXP_SUBSTR(colb, '[^ ]+', 1, 3) like '%'|| cola||'S%' then 3
      when REGEXP_SUBSTR(colb, '[^ ]+', 1, 4) like '%'|| cola||'S%' then 4  
      when REGEXP_SUBSTR(colb, '[^ ]+', 1, 5) like '%'|| cola||'S%' then 5 
     when REGEXP_SUBSTR(colb, '[^ ]+', 1, 6) like '%'|| cola||'S%' then 6
     end) )
FROM tname ;

Upvotes: 0

Related Questions