<div>
For such simple situation you don't need regexp. You could use INSTR instead.<br />
<br />
select case when INSTR(UPPER(STRING_VALUE),<wbr /> 'HIGH') = 0 then NULL else 'HIGH' end,<br />
case when INSTR(UPPER(STRING_VALUE),<wbr /> 'LOW') = 0 then NULL else 'LOW' end<br />
from your_table
</div>


mohammadzahid

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


<div>
OK, Alex, you are right.<br />
<br />
Still simple, but restricted solution could be done without regexp:<br />
<br />
select case when INSTR(UPPER(STRING_VALUE),<wbr /> ' HIGH ') = 0 then NULL else 'HIGH' end,<br />
case when INSTR(UPPER(STRING_VALUE),<wbr /> ' LOW ') = 0 then NULL else 'LOW' end<br />
from your_table<br />
<br />
And regexp makes it more reliable<br />
<br />
select case when REGEXP_INSTR(STRING_VALUE,<wbr /> '\bHIGH\b',1,1,0,'i') = 0 then NULL else 'HIGH' end,<br />
case when REGEXP_INSTR(STRING_VALUE,<wbr /> '\bLOW\b',1,1,0,'i') = 0 then NULL else 'LOW' end<br />
from your_table
</div>


<div>
Alex,<br />
<br />
What if the string reads &quot;1. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGHLANDER-LOW FLOOR&quot;?<br />
:)<br />
Both your solutions won't work for it.
</div>


<div>
They DO!<br />
These cases should be mentioned by the OP's requirements!<br />
<br />
<a href="http://www.sqlfiddle.com/#!4/4d2b10/2" rel="ugc">http://www.sqlfiddle.com/#!4/4d2b10/2</a>
</div>


<div>
<pre><code class="code-10 nolinks" id="code-20-42768130-1"><span>CREATE TABLE test (str varchar2(4000));</span>
<span>	</span>
<span>INSERT INTO test(str) VALUES ('1. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGH FLOOR');</span>
<span>INSERT INTO test(str) VALUES ('2. TIRE, MICHELIN 305/70R22.5 X INCITY Z LOW FLOOR');</span>
<span>INSERT INTO test(str) VALUES ('3. TIRE, MICHELIN 305/70R22.5 X INCITY Z HIGHLANDER LOW FLOOR');</span>
<span>INSERT INTO test(str) VALUES ('4. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGHLANDER-LOW FLOOR');</span>
<span></span>
<span>select str,</span>
<span>       case</span>
<span>         when UPPER(str) like '% HIGH %' then</span>
<span>          'HIGH'</span>
<span>         else</span>
<span>          case</span>
<span>            when UPPER(str) like '% LOW %' then</span>
<span>             'LOW'</span>
<span>            else</span>
<span>             null</span>
<span>          end</span>
<span>       end high_or_low,</span>
<span>       coalesce(regexp_substr(str, ' HIGH '), regexp_substr(str, ' LOW '))</span>
<span>  from test;</span>
</code></pre>
</div>


<div>
let's leave the author of the question to decide whether it works or not
</div>


<div>
<blockquote>
whether it works or not
</blockquote>
Often a point of view without knowing all requirements ;-)
</div>


<div>
it is looking for space value space you could just change it to value space or just value<br />
<br />
select str,<br />
&nbsp; &nbsp; &nbsp; &nbsp;case<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;when UPPER(str) like '%HIGH %' then<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'HIGH'<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;else<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; case<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; when UPPER(str) like '%LOW %' then<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;'LOW'<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; else<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;null<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; end<br />
&nbsp; &nbsp; &nbsp; &nbsp;end high_or_low,<br />
&nbsp; &nbsp; &nbsp; &nbsp;coalesce(regexp_substr(str<wbr />, 'HIGH '), regexp_substr(str, 'LOW '))<br />
&nbsp; from test;
</div>


<div>
Maybe the asker isn't even looking for the words &quot;HIGH&quot; and &quot;LOW&quot; specifically, but merely the word before the word FLOOR<br />
or the 2nd to last word<br />
or the word after Z<br />
or the first word after character 38<br />
or ... (I could keep going with other variations that would all return the 2 requested values)<br />
<br />
<br />
As mentioned above, mohammadzahid, your question needs more detail in order to provide a meaningful answer.
</div>


<div>
<blockquote>
As mentioned above, mohammadzahid, your question needs more detail in order to provide a meaningful answer. 
</blockquote>
Yes yes yes ;-)<br />
What are your specific requirements and your expected results in detail? Then, and only then, we're able to give you a proper solution!
</div>


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


<div>
Then the first version solves this problem
</div>


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


<div>
If you’re really just looking for the words HIGH and LOW, then you should not use regexp_substr.<br />
The instr/case solution in the earlier responses is more efficient.<br />
<br />
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.
</div>


<div>
Thank you all for your comments.
</div>


<div>
<div class="content wysiwyg-content">
Hi, <br />
I'm looking for a regexp_substr result = HIGH and LOW from the following two strings <br />
<br />
1. TIRE, MICHELIN 305/85R22.5 X INCITY Z HIGH FLOOR<br />
2. TIRE, MICHELIN 305/70R22.5 X INCITY Z LOW FLOOR<br />
<br />
Output:<br />
HIGH <br />
LOW <br />
<br />
This can be 2 separate SQL statement regexp_substr. <br />
<br />
Thanks,
</div>
</div>


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,

Looking for REGEXP_SUBSTR SQL statement to get a result

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

Oracle Database

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.