CODEWITHSUNDEEP
sqlsql-servert-sqlsubqueryexists
Bodhi
Bodhi

Reputation: 548

How to query rows matching for a particular string?

This is how table looks like and here Empid and idnumber were unique for each employee and also an employee can have multiple badges with different badge number.

enter image description here

Now,I want to filter employees whose badge start with 6542 and 3214, ie employees carrying both badge starting from 6542 and 3214

Thank u

UPdate 1

There are some records which is having only a single badge starting from 6542 or 3214 . but I want only employees who is carrying both badges .

Upvotes: 0

Views: 53

Answers (5)

Ravi
Ravi

Reputation: 1172

just simply do like this 

    Select a.empid, a.name 
    from TableName as a
    inner join TableName as b on a.Empid = b.Empid and a.idnumber = b.idnumber and b.badge like '3214%'
    where a.badge like '6542%'

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

We can use COUNT DISTINCT with CASE in HAVING as below

DECLARE @test AS TABLE(EMPID INT, Badge VARCHAR(50), idNumber INT, EName VARCHAR(50))
INSERT INTO @test VALUES
(1148, '6542-74488', 66448, 'Adam Jhon'),
(1148, '642-8562', 66448, 'Adam Jhon'),
(1148, '3214-52874', 66448, 'Adam Jhon'),

(1149, '3214-45220', 209541, 'Tom Koyaski'),
(1150, '3214-23134', 63339, 'Shirin Abdulla'),
(1151, '3214-42355', 65498, 'Linda Jhon'),

(1151, '6542-2546', 65498, 'Linda Jhon'),
(1152, '3214-47632', 208673, 'Gayeth'),
(1153, '6542-73085', 83209, 'Maria Smith'),

(1153, '3214-58073', 65498, 'Maria Smith'),
(1154, '3214-26735', 208673, 'Ayan Jacob'),
(1155, '642-26739', 53959, 'Wo Li')

SELECT empid, Ename 
FROM @test 
WHERE badge LIKE '6542%' OR badge LIKE '3214%'
GROUP BY empid, Ename
HAVING COUNT (DISTINCT(CASE WHEN badge like '6542%' THEN 1 
                WHEN badge LIKE '3214%' THEN 2 END))>1

OUTPUT:

empid   Ename
1148    Adam Jhon
1151    Linda Jhon
1153    Maria Smith

Upvotes: 0

Nikhil
Nikhil

Reputation: 3950

this will work:

select empid from table_name where regexp_like(Badge,'^(6542)(.*)$')
intersect
select empid from table_name where regexp_like(Badge,'^(3214)(.*)$');

sql server equivalent:

select empid from table_name where PATINDEX ('^(6542)(.*)$',Badge)  !=0
intersect
select empid from table_name where PATINDEX ('^(6542)(.*)$',Badge)  !=0

Upvotes: 0

user10679624
user10679624

Reputation:

Just use like on badge field

    Select empid, name 
    from TableName 
    where badge like '6542%' or badge like '3214%'
    group by empid, name
    having count(*)>1

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Do a GROUP BY, use HAVING to ensure both badges:

select empid, name
from Table
where badge like '6542-%' or badge like '3214-%'
group by empid, name
having count(distinct badge) > 1

Or use INTERSECT:

select empid, name from Table where badge like '6542-%'
intersect
select empid, name from Table where badge like '3214-%'

Upvotes: 1

Related Questions