CODEWITHSUNDEEP
sql-server
Abraham1973
Abraham1973

Reputation: 31

How to use LIKE to extract specific ID row in SQL Server query

I have this issue around this SQL script that I want your assistance with.

I have records with the following data:

ID         AGE
-----------------
AB1001      20
AB1002      21
OPEI112     12
ABI1001     25
ABI1002     28
OPEI220     14
AB1003      30
TI2100      19
AB1004      32
ABI1003     26
ABI1004     10

I want to display all ID's start with ABI,OPEI,TI I don't want to see ID's that start with AB. I use a SQL SELECT statement, I have tried the following:

SELECT ID 
FROM TableID 
WHERE ID LIKE 'ABI%'AND ID LIKE 'OPEI%' AND ID LIKE 'TI%'

This didn't return the expected output because it contain AB, so I tried this which displays all the records

SELECT ID 
FROM TableID 
WHERE ID NOT LIKE 'AB%'

This too didn't return the expected result because it also exclude the ID's with ABI, but displays all the records.

I need any suggestion that will display all record ID's without AB

Thank you

Upvotes: 0

Views: 1337

Answers (4)

Abraham1973
Abraham1973

Reputation: 31

Select * from TableID where ID not in (AB1001,AB1002,ABI1001,ABI1002,AB1003,AB1004,ABI1003 ,ABI1004 )

Upvotes: 0

Russell Fox
Russell Fox

Reputation: 5435

You just need to change your "AND"s to "OR"s:

SELECT ID 
FROM TableID 
WHERE ID LIKE 'ABI%'
    OR ID LIKE 'OPEI%' 
    OR ID LIKE 'TI%';

Upvotes: 0

PrathapG
PrathapG

Reputation: 789

See your first query , You need to use OR statement instead of AND

as below ,

SELECT ID 
FROM TableID 
WHERE ID LIKE 'ABI%'OR ID LIKE 'OPEI%' OR ID LIKE 'TI%'

Upvotes: 2

Suraj Kumar
Suraj Kumar

Reputation: 5653

Try this

create table #temp (id varchar(20), age int)
insert into #temp values 
('AB1001', '20'), ('AB1002', 21), ('ABI1001', 25), ('ABI1002', 28), ('AB1003', 30), ('AB1004', 32),
('ABI1003', 26), ('ABI1004', 10)

select * from #temp where id like 'ABI%'

drop table #temp

The output is as follows

id      age
ABI1001 25
ABI1002 28
ABI1003 26
ABI1004 10

Here all id starts with ABI.

Upvotes: 1

Related Questions