What's the best way to check if characters exist in string in SQL?

Question

I have strings like:

t.reported_name
-------------------------
    D3O using TM-0549 - Rev # 6
    D3O using TM-0549 - Rev # 6
    Water using TM-0415 - Rev #10
    Water using TM-0449 - Rev # 10
    Decanoic Acid using LL-1448 - Rev# 2
    DBE-821 using QCRM-0015 - Rev#1
    Water using TM-0441 Rev # 10
    FC Sessile Drop Contact Angle using MTM-017_REV_B - Rev # 1
    IPA using QCRM-0017
    Norbloc using TM-0501 - Rev # 5
    DK (non-edge corrected) using TM-0534 - Rev # 3
    Decanoic Acid_L3 using LL-1448_L3
    Decanoic Acid_L4 using LL-1448_L4
    MXP7-1911 using CRM-0239
    TMPTMA using TM-0515 - Rev# 8
    DK (edge corrected) using MTM-09 - Rev# 0

I need to extract test method (anything after 'using') with number, i.e: TM-0549 and Revision number, i.e: Rev # 6

select  distinct 
     case when REGEXP_LIKE(t.reported_name,'TM-', 'c') THEN SUBSTR(t.reported_name, INSTR(t.reported_name, 'TM'), 7) END test_method,
     case when INSTR(t.reported_name,'Rev #') = 1 THEN SUBSTR(t.reported_name, INSTR(t.reported_name, 'Rev'), 7) END revision_number
from test s

from above data I want:

test_method  revision_number

TM-0549     Rev # 6
TM-0549     Rev # 6
TM-0415     Rev #10
TM-0449     Rev # 10
LL-1448     Rev# 2
QCRM-0015   Rev#1
TM-0441     Rev # 10
MTM-017_REV_B  Rev # 1
QCRM-0017   null
TM-0501     Rev # 5
TM-0534     Rev # 3
LL-1448_L3  null
LL-1448_L4  null
CRM-0239    null
TM-0515     Rev# 8
MTM-09      Rev# 0

Why instr doesn't work? instr(reported_name, 'TM-') > 0 is not working? — vav
– vav, Commented Jun 3, 2014 at 12:38

Frank Schmitt · Accepted Answer · 2014-06-03 14:52:13Z

1

Assuming

for test_method, we want to match TM-<number>
for revision, we want to match Rev # <number> (note the spaces around #)

Then here's a solution with REGEXP_SUBSTR:

select 
  regexp_substr(reported_name, 'TM\-[0-9]+') as test_method_regexsub,
  regexp_substr(reported_name, 'Rev # [0-9]+') as revision_regexsub            
from test t

And here's another one with REGEXP_REPLACE; we have to use the CASE/REGEXP_LIKE workaround to return an empty string if the regex doesn't match, because REGEXP_REPLACE returns the whole string unchanged if no match is found:

select 
  (case 
     when regexp_like(reported_name, '.*(TM\-[0-9]+).*') 
     then regexp_replace(reported_name, '.*(TM\-[0-9]+).*', '\1') 
     else '' 
   end)  as test_method_regexrepl,
  (case 
     when regexp_like(reported_name, '.*(Rev # [0-9]+).*') 
     then regexp_replace(reported_name, '.*(Rev # [0-9]+).*', '\1') 
     else '' 
   end)  as revision_regexrepl
from test t

The second approach uses a capturing group (Rev # [0-9]+) and replaces the whole string with its contents \1.

2nd UPDATE

Assuming

everything in front of using should be ignored
everything up to an optional Rev is the test method name
a revision consists of Rev # <number>, where the first space is optional

this should work:

select reported_name,
       (case 
          when regexp_like(reported_name, '.* using (.*)( - Rev.*)') 
            then regexp_replace(reported_name, '.* using (.*)( - Rev.*)', '\1') 
          when regexp_like(reported_name, '.* using (.*)') 
            then regexp_replace(reported_name, '.* using (.*)', '\1') 
          else '' end)  as test_method_regexrepl,
       (case when regexp_like(reported_name, '.* - (Rev[ ]?# [0-9]+)') 
          then regexp_replace(reported_name, '.*(Rev[ ]?# [0-9]+)', '\1') 
          else '' end)  as revision_regexrepl
from test t

Explanation:

.* using (.*)( - Rev.*) is our regex for a test method that has a revision. It matches
- an arbitrary string .*
- the string using (note the two spaces)
- an arbitrary string (.*) - we use the parentheses () to capture this part of the match in a group
- the string - Rev, followed by an arbitrary string; again, we use parentheses to capture the string in a group (although we don't really need that)
If we have a match, we replace the whole string with the first capturing group \1 (this contains the part between using and Rev
.* using (.*) is our fallback for a test method without the revision; it matches
- an arbitrary string .*
- the string using (note the two spaces)
- an arbitrary string (.*) - we use the parentheses () to capture this part of the match in a group
If we have a match, we replace the whole string with the first capturing group \1 (this contains the part between using and Rev
.* - (Rev[ ]?# [0-9]+) is our regex for the revision part. It matches
- an arbitrary string followed by a hyphen surrounded by spaces .* -
- the word Rev
- an optional space [ ]?
- a lattice followed by a space #
- one or more digits [0-9]+ and again uses a capturing group (Rev...) for the "interesting" part
If we have a match, we replace the whole string with the first capturing group \1 (this contains the part between Rev and the last digit)

SQL Fiddle

edited Jun 3, 2014 at 14:52

answered Jun 3, 2014 at 14:00

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

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

7 Comments

Angelina Over a year ago

I just learned that test_method is anything after 'using' and I also have cases when Rev is like: 'DAROCUR using TM-0515 - Rev# 8'

Angelina Over a year ago

thank you so much. Do you mind throwing a word or two on what's exactly happening in here :)

Angelina Over a year ago

Using your obve query, I noticed that in some cases, like here: 'D3O using TM-0549 Rev #4 - Analyst 1 Retest', my test_method_regexrepl comes out:'TM-0549 Rev #4 - Analyst 1 Retest', and revision_regexrepl: null. AND in case where: 'D3O using TM-0549 - Rev # 4 Analyst 2 Retest' my revision_regexrepl: 'Rev # 4 Analyst 2 Retest'

Frank Schmitt Over a year ago

@Angelina I've added an explanation

Frank Schmitt Over a year ago

@Angelina To account for an optional part after the Rev, just add a .* at the end of the regex.

|

vav · Accepted Answer · 2014-06-03 12:46:25Z

1

TMPTMA is not matched by your regexp, instr is working:

select 'regexp match' descr, count(*) 
from dual where regexp_like('TM-PTMA', 'TM-') 
union all
select 'regexp no match', count(*) 
from dual where regexp_like('TMPTMA', 'TM-') 
union all
select 'instr no match', count(*) 
from dual where instr('TMPTMA', 'TM-') > 0
union all
select 'instr match', count(*) 
from dual where instr('TM-PTMA', 'TM-') > 0

Output:

DESCR             COUNT(*)
--------------- ----------
regexp match             1 
regexp no match          0 
instr no match           0 
instr match              1

answered Jun 3, 2014 at 12:46

vav

4,6942 gold badges22 silver badges40 bronze badges

Comments

Rost · Accepted Answer · 2014-06-03 15:21:16Z

As for me for particular situation query can look like:

WITH j
     AS (SELECT 'D3O using TM-0549 - Rev # 6' str FROM DUAL
         UNION ALL
         SELECT 'D3O using TM-0549 - Rev # 6' FROM DUAL
         UNION ALL
         SELECT 'Water using TM-0415 - Rev #10' FROM DUAL
         UNION ALL
         SELECT 'Water using TM-0449 - Rev # 10' FROM DUAL
         UNION ALL
         SELECT 'Decanoic Acid using LL-1448 - Rev# 2' FROM DUAL
         UNION ALL
         SELECT 'DBE-821 using QCRM-0015 - Rev#1' FROM DUAL
         UNION ALL
         SELECT 'Water using TM-0441 Rev # 10' FROM DUAL
         UNION ALL
         SELECT 'FC Sessile Drop Contact Angle using MTM-017_REV_B - Rev # 1' FROM DUAL
         UNION ALL
         SELECT 'IPA using QCRM-0017' FROM DUAL
         UNION ALL
         SELECT 'Norbloc using TM-0501 - Rev # 5' FROM DUAL
         UNION ALL
         SELECT 'DK (non-edge corrected) using TM-0534 - Rev # 3' FROM DUAL
         UNION ALL
         SELECT 'Decanoic Acid_L3 using LL-1448_L3' FROM DUAL
         UNION ALL
         SELECT 'Decanoic Acid_L4 using LL-1448_L4' FROM DUAL
         UNION ALL
         SELECT 'MXP7-1911 using CRM-0239' FROM DUAL
         UNION ALL
         SELECT 'TMPTMA using TM-0515 - Rev# 8' FROM DUAL
         UNION ALL
         SELECT 'DK (edge corrected) using MTM-09 - Rev# 0' FROM DUAL)

SELECT TRIM(RTRIM(TRIM (SUBSTR (clear_str, 0, INSTR (clear_str, ' ') + LENGTH (' '))),'-')) AS left_str,
       TRIM(LTRIM(TRIM (SUBSTR (clear_str, INSTR (clear_str, ' ') + LENGTH (' '))),'-')) AS right_str
  FROM (SELECT TRIM (SUBSTR (str, INSTR (str, 'using') + LENGTH ('using'))) || ' ' clear_str FROM j)

UPD. Also, this solutions not depends on business data like 'Rev' or something else. But it is sensitive to spaces in our test_method value

Collectives™ on Stack Overflow

What's the best way to check if characters exist in string in SQL?

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related