Skip to content
Advertisement

How to get only comma values in SQL Server

I have a employee table and I am trying to get only comma values in SQL Server.

Emp table:

Eid Ename
1 Peter,J
2 Mike,S
3 ,
4 ,,,,,,

I tried this code:

SELECT
    (LEN(ename) - LEN(REPLACE(ename, ',', ''))
FROM emp;

I am not getting the length 0 if the values contain only commas.

Expected result: I want only 3 and 4 emp ids.

Advertisement

Answer

select x.eid,x.ename 
from
(
select 1 eid,'Peter,J'  ename union
select 2 eid,'Mike,S' ename union
select 3 eid, ','      ename  union
select 4 eid,',,,,,'  ename 
) x
where len(x.ename)>0 and len( replace(x.ename,',','') ) =0

The answer there is condition line “where ….”

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement