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