Searching for multiple strings in Oracle SQL

Question

I am trying to search for a list of different strings within a table that is constrained on the REGEXP_SUBSTR() function.

What my code below is doing is it removes everything that is contains 'PO:' and that contains 'HF:' and searches for a number (ex: 12345) within the table. I want a quick way to search for multiple numbers (ex; 12345, 12346, 12347...) and not have to use the REGEXP_SUBSTR() function with LIKE each time.

My code:

SELECT column_1, column_2, column_3    
FROM table_1
WHERE column_1 NOT LIKE 'PO:%'
AND column_1 NOT LIKE 'HF:%'
AND REGEXP_SUBSTR(column_1, '[0-9]+',1,1) LIKE '%12345%'
;

I have tried 'IN'

AND REGEXP_SUBSTR(column_1, '[0-9]+',1,1) **IN** ('%12345%', '%12346%')

but this doesn't work.

I am new so any help/guidance is much appreciated!

score 1 · Answer 1 · answered Jun 27 '18 at 18:15

There's already a post that should answer this question. You're looking for a wildcard join.

Joining tables with LIKE (SQL)

select * 
from tableone 
   join tabletwo on tableone.pizzaone like ('%' || tabletwo.pizzatwo || '%')

Here's a dirty example using t-sql.

create table #temp1 
(col1 varchar(15))
insert into #temp1 values 
 ('abcde') --contains 'ab'
,('bcde')  --does not contain 'ab' or 'ef'
,('defgh') --contains 'ef'

 select 
    a.col1 
    from #temp1 a
    inner join 
    (
    select 'ab' as col2 
    union 
    select 'ef' as col2 
    ) b
    on a.col1 like '%' + b.col2 + '%'

What we're doing here is looking at two tables. The first table is your main table. The second table, in this case we're just looking at a union, contains the values you are looking to match. You are joining your first table to the second table on the two columns like you normally would. However, you can replace the = sign with a like statement. Depending on which flavor of SQL you are working with, you will use the appropriate concat function to wrap your search string in %. In Oracle, you would use pipes, in sql server, plus signs.

Gordon Linoff · Answer 2 · 2018-06-28T01:25:15.483

1

How about regexp_like():

SELECT column_1, column_2, column_3    
FROM table_1
WHERE column_1 NOT LIKE 'PO:%' AND
      column_1 NOT LIKE 'HF:%' AND
      regexp_like(column_1, '12345|12346|12347')

edited Jun 28 '18 at 01:25

answered Jun 27 '18 at 18:15

Gordon Linoff

1,242,037
58
646
786

The spaces in your regex look wrong - shouldn't this rather be `12345|12346|12347` to ensure values like `AB:12346` are also matched? – Frank Schmitt Jun 27 '18 at 18:50

score -1 · Accepted Answer · answered Jun 27 '18 at 18:00

Is this what you are looking for ?

SELECT
    *
FROM
    (
        SELECT
            'PO123455HF' AS str
        FROM
            dual
        UNION
        SELECT
            'PO124455HF' AS str
        FROM
            dual
        UNION
        SELECT
            'PO1236855HF' AS str
        FROM
            dual
        UNION
        SELECT
            'PO1243545HF' AS str
        FROM
            dual
        UNION
        SELECT
            'POHF' AS str
        FROM
            dual
    )
WHERE
    REGEXP_LIKE ( str,
                  '[0-9]' );

Output -

PO123455HF
PO1236855HF
PO1243545HF
PO124455HF

Searching for multiple strings in Oracle SQL

3 Answers3