CODEWITHSUNDEEP
sql-server
Malay Dave
Malay Dave

Reputation: 115

How to find remaining alphabets?

I have one table and i want to write query to fetch data 

empno empname 
  1     abc
  2     xyz
  3     mnc
  4     pqr

Now i want to write query those alphabets which is not used in empname as first letter like

b,c,d,e,f,g,h,i,j,k,l,n,o,q,r,s,t,u,v,w,y,z

I don't need that alphabets which is used in first letter of empname

a,x,m,p

so how can i write query for this ?

Thanks in advance

Upvotes: 0

Views: 466

Answers (2)

Ullas
Ullas

Reputation: 11556

First just use a table variable to store all the alphabets like below.

declare @alphabets as table(letter varchar(1));

insert into @alphabets values
('a'),
('b'),
('c'),
('d'),
('e'),
('f'),
('g'),
('h'),
('i'),
('j'),
('k'),
('l'),
('m'),
('n'),
('o'),
('p'),
('q'),
('r'),
('s'),
('t'),
('u'),
('v'),
('w'),
('x'),
('y'),
('z');

Then use NOT EXISTS to find the missing alphabets from the table variable with the first character of empname and use STUFF to concatenate all with comma.

Query

select stuff((select ', ' + t.letter from(
    select * from @alphabets a
    where not exists(
        select 1 from [your_table_name] e
        where a.letter = left(e.empname, 1)
    )
)t
for xml path('')
), 1, 2, '');

Demo

Upvotes: 2

Vishal Khunt
Vishal Khunt

Reputation: 170

Use bellow query

select * from employee
  where empname not like 'a%' 
    and empname not like 'x%' 
    and empname not like 'm%' 
    and empname not like 'p%'

here 's%' use for select first character of the name.

Upvotes: 1

Related Questions