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 ….”