Extracting a String Portion using Regular Expressions in Oracle

Question

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

The posted question does not appear to include any attempt at all to solve the problem. StackOverflow expects you to try to solve your own problem first, as your attempts help us to better understand what you want. Please edit the question to show what you've tried, so as to illustrate a specific problem you're having in a minimal reproducible example. For more information, please see How to Ask and take the tour. — CertainPerformance
– CertainPerformance, Commented Sep 18, 2018 at 5:56
Please give us the exact rules by which you would know to extract/find the value BAN27S001 in your string. Without knowing this logic, we can't help you. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 18, 2018 at 5:57
Thanks Tim for looking into it.. I have added the screenshot of how my data looks like.. In the screenshot, the portion I want to extract is prefixed with Col A. For example.. I need to extract BAN27S001 from Col B and I have my Col A portion as well in the string that I am looking to extract. Now is it possible to extract the portion of String from Col B using Col A? Please let me know if the information works.. — san
– san, Commented Sep 18, 2018 at 6:23
So, you want to match BAN + digits + S + digits? What pattern have you tried? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Sep 18, 2018 at 6:32
Yes Wiktor.. That is what I am looking for. I am new to this and I tried with patterns like '(\S*)'. The portion of the string that I am expecting to extract will not always be in the same position. Please see the last row in the screenshot. It is in the beginning itself. — san
– san, Commented Sep 18, 2018 at 6:41

Barbaros Özhan · Accepted Answer · 2018-09-18 15:12:34Z

1

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

edited Sep 18, 2018 at 15:12

answered Sep 18, 2018 at 7:09

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

san Over a year ago

Thanks Barbaros.. That worked.. Please see my last 3 rows in the 2nd screenshot. I need to extract 177-130 and 177-131 from Col B.. My data also has 524 - 23 (spaces between hyphens). Is there a way to handle this.. I tried with this.. ^[^-]*[^ -].. I guess I am not handling this part in a right way to the statement provided by you,. could you please advise..

Ajay Venkata Raju · Accepted Answer · 2018-09-18 07:24:39Z

0

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 ;

edited Sep 18, 2018 at 7:24

answered Sep 18, 2018 at 7:17

Ajay Venkata Raju

1,16813 silver badges22 bronze badges

Collectives™ on Stack Overflow

Extracting a String Portion using Regular Expressions in Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related