Looking for REGEXP_SUBSTR SQL statement to get a result

Hi,
I'm looking for a regexp_substr result = HIGH and LOW from the following two strings

1. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGH FLOOR
2. TIRE, MICHELIN 305/70R22.5 X INCITY Z LOW FLOOR

Output:
HIGH
LOW

This can be 2 separate SQL statement regexp_substr.

Thanks,
LVL 11
mohammadzahidAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OMC2000Commented:
For such simple situation you don't need regexp. You could use INSTR instead.

select case when INSTR(UPPER(STRING_VALUE), 'HIGH') = 0 then NULL else 'HIGH' end,
case when INSTR(UPPER(STRING_VALUE), 'LOW') = 0 then NULL else 'LOW' end
from your_table
Alex [***Alex140181***]Software DeveloperCommented:
@OMC2000: That won't work properly! What if the string reads "1. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGHLANDER LOW FLOOR" ?!
OMC2000Commented:
OK, Alex, you are right.

Still simple, but restricted solution could be done without regexp:

select case when INSTR(UPPER(STRING_VALUE), ' HIGH ') = 0 then NULL else 'HIGH' end,
case when INSTR(UPPER(STRING_VALUE), ' LOW ') = 0 then NULL else 'LOW' end
from your_table

And regexp makes it more reliable

select case when REGEXP_INSTR(STRING_VALUE, '\bHIGH\b',1,1,0,'i') = 0 then NULL else 'HIGH' end,
case when REGEXP_INSTR(STRING_VALUE, '\bLOW\b',1,1,0,'i') = 0 then NULL else 'LOW' end
from your_table
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Alex [***Alex140181***]Software DeveloperCommented:
@OMC2000: sorry, still too hardcoded and unnecessarily "complex"...

Built-In solution:
case
         when UPPER(str) like '% HIGH %' then
          'HIGH'
         else
          case
            when UPPER(str) like '% LOW %' then
             'LOW'
            else
             null
          end
       end

Open in new window


REGEXP_SUBSTR solution:
coalesce(regexp_substr(STRING_VALUE, ' HIGH '), regexp_substr(STRING_VALUE, ' LOW '))

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OMC2000Commented:
Alex,

What if the string reads "1. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGHLANDER-LOW FLOOR"?
:)
Both your solutions won't work for it.
Alex [***Alex140181***]Software DeveloperCommented:
They DO!
These cases should be mentioned by the OP's requirements!

http://www.sqlfiddle.com/#!4/4d2b10/2
Alex [***Alex140181***]Software DeveloperCommented:
CREATE TABLE test (str varchar2(4000));
	
INSERT INTO test(str) VALUES ('1. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGH FLOOR');
INSERT INTO test(str) VALUES ('2. TIRE, MICHELIN 305/70R22.5 X INCITY Z LOW FLOOR');
INSERT INTO test(str) VALUES ('3. TIRE, MICHELIN 305/70R22.5 X INCITY Z HIGHLANDER LOW FLOOR');
INSERT INTO test(str) VALUES ('4. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGHLANDER-LOW FLOOR');

select str,
       case
         when UPPER(str) like '% HIGH %' then
          'HIGH'
         else
          case
            when UPPER(str) like '% LOW %' then
             'LOW'
            else
             null
          end
       end high_or_low,
       coalesce(regexp_substr(str, ' HIGH '), regexp_substr(str, ' LOW '))
  from test;

Open in new window

OMC2000Commented:
let's leave the author of the question to decide whether it works or not
Alex [***Alex140181***]Software DeveloperCommented:
whether it works or not
Often a point of view without knowing all requirements ;-)
David Johnson, CD, MVPOwnerCommented:
it is looking for space value space you could just change it to value space or just value

select str,
       case
         when UPPER(str) like '%HIGH %' then
          'HIGH'
         else
          case
            when UPPER(str) like '%LOW %' then
             'LOW'
            else
             null
          end
       end high_or_low,
       coalesce(regexp_substr(str, 'HIGH '), regexp_substr(str, 'LOW '))
  from test;
sdstuberCommented:
Maybe the asker isn't even looking for the words "HIGH" and "LOW" specifically, but merely the word before the word FLOOR
or the 2nd to last word
or the word after Z
or the first word after character 38
or ... (I could keep going with other variations that would all return the 2 requested values)


As mentioned above, mohammadzahid, your question needs more detail in order to provide a meaningful answer.
Alex [***Alex140181***]Software DeveloperCommented:
As mentioned above, mohammadzahid, your question needs more detail in order to provide a meaningful answer.
Yes yes yes ;-)
What are your specific requirements and your expected results in detail? Then, and only then, we're able to give you a proper solution!
mohammadzahidAuthor Commented:
Thanks for all your comments, I only need to extract HIGH or LOW from the string excluding space before and after.
OMC2000Commented:
Then the first version solves this problem
mohammadzahidAuthor Commented:
I agree, but I'm looking for a SELECT using regexp_substr to extract only HIGH and LOW.
sdstuberCommented:
If you’re really just looking for the words HIGH and LOW, then you should not use regexp_substr.
The instr/case solution in the earlier responses is more efficient.

For small strings and a small number of rows you won’t notice the difference, but as your data volume goes up, the cost difference will begin to degrade performance.
mohammadzahidAuthor Commented:
Thank you all for your comments.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle12c

From novice to tech pro — start learning today.