CODEWITHSUNDEEP
sqldb2
user15973370
user15973370

Reputation:

How to select the strings that only begin with specific prefixes

I'm trying to filter my query to only get the string that begin with these prefixes :

**AC / AY / AN / AZ / IR **

To do so i tried this SQL query :

select PN from MyTestTable where PN like 'AC%'  AND  PN like 'AY%' AND  PN like 'AN%' AND  PN like 'AZ%' AND  PN like 'IR %' ;

Is there a way to filter using a list or an other way ?

Upvotes: 0

Views: 1124

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can use like with a bunch of or conditions. Or using regexp_like():

select PN
from MyTestTable
where regexp_like(PN, '^(AC|AY|AN|AZ|IR )')

Note: This includes the space after 'IR' which appears to be part of the question. If it is not needed, you could also use:

where left(PN, 2) in ('AC', 'AY', 'AN', 'AZ', 'IR')

Upvotes: 0

Osama Al-Banna
Osama Al-Banna

Reputation: 1515

You can do it using the below query:

SELECT PN FROM MyTestTable WHERE SUBSTR(PN,1,2) IN ('AC','AY','AN','AZ','IR')

I hope this work for you .

Upvotes: 1

Backs
Backs

Reputation: 24913

Create temp table and use join:

DECLARE @Pref TABLE (val nvarchar(20))
INSERT INTO @Pref
VALUES
('AC'),
('AY'),
('AN'),
('AZ'),
('IR')

SELECT t.PN 
FROM MyTestTable AS t
INNER JOIN @Pref p ON t.PN LIKE p.val+'%'

Upvotes: 0

Related Questions