Looking for REGEXP_SUBSTR SQL statement to get a result

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

@OMC2000: That won't work properly! What if the string reads "1. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGHLANDER LOW FLOOR" ?!

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

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.

They DO!
These cases should be mentioned by the OP's requirements!

http://www.sqlfiddle.com/#!4/4d2b10/2

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;

Select allOpen in new window

let's leave the author of the question to decide whether it works or not

whether it works or not

Often a point of view without knowing all requirements ;-)

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;

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.

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!

Thanks for all your comments, I only need to extract HIGH or LOW from the string excluding space before and after.

Then the first version solves this problem

I agree, but I'm looking for a SELECT using regexp_substr to extract only HIGH and LOW.

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.

Thank you all for your comments.