StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This sounds like the perfect use-case for <a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/conditions007.htm#SQLRF00501" rel="nofollow"><code>REGEXP_LIKE()</code></a> rather than having large number of OR operators.</p> <p>Given the following table:</p> <pre><code>create table t1 ( str varchar2(100)); insert into t1 values (','); insert into t1 values ('#'); insert into t1 values ('-'); insert into t1 values ('$'); insert into t1 values ('N/A'); insert into t1 values ('UNKnoWN'); insert into t1 values ('XYZ'); insert into t1 values ('abc'); insert into t1 values ('fdg'); </code></pre> <p>The following query fulfils your requirements around specific strings:</p> <pre><code>select * from t1 where regexp_like(str, '([-#,]|xyz|unknown|n/a)','i') </code></pre> <p>The <code>'i'</code> means that it performs case-insensitive matching.</p> <p>Slightly more complicated is your requirement that the string contains all one character. If we add the following rows:</p> <pre><code>insert into t1 values ('rrrr'); insert into t1 values ('ggg'); </code></pre> <p>Then the following query will also include these, I use <a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions147.htm#SQLRF20014" rel="nofollow"><code>REGEXP_COUNT()</code></a> to ensure that the number of characters in the string that are the same as the first character match the length of the string:</p> <pre><code>select * from t1 where regexp_like(str, '([-#,]|xyz|unknown|n/a)','i') or regexp_count(str, substr(str,1,1)) = length(str) </code></pre> <p>If you need <code>'N/A'</code>, <code>'XYZ</code> and <code>'UNKNOWN'</code> to match only if there is nothing else in the string use the <code>^</code> and <code>$</code> meta-symbols, which ensure that it matches only at the beginning and end of the string:</p> <pre><code>insert into t1 values ('g UNKnoWN'); </code></pre> <p>adding one more test case</p> <pre><code> select * from t1 where regexp_like(str, '([-#,]|^(xyz|unknown|n/a)$)','i') or regexp_count(str, substr(str,1,1)) = length(str) </code></pre> <p>Here's a <a href="http://www.sqlfiddle.com/#!4/acadb/2" rel="nofollow">SQL Fiddle</a> to demonstrate.</p> <p>Regular expressions can be extremely powerful, but they come with their own limitations. I would highly recommend reading the linked documentation.</p> <hr> <p>Your current query throws an error because it's incorrect. You need to test the column multiple times:</p> <pre><code>SELECT * FROM table_name WHERE column_1 LIKE '%-%' OR column_1 LIKE '%#%' </code></pre> <p>Note that <code>column_1</code> is included twice.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload