CODEWITHSUNDEEP
sqloracle-database
alex_fields1
alex_fields1

Reputation: 71

Searching for multiple strings in Oracle SQL

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!

Upvotes: 0

Views: 7123

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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')

Upvotes: 1

Andrew
Andrew

Reputation: 373

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.

Upvotes: 1

shrek
shrek

Reputation: 887

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

Upvotes: -1

Related Questions