How to get only comma values in SQL Server

Question

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.

Answer 1

If you're after rows that only contain commas you can use some pattern matching:

SELECT ID, ename
FROM dbo.YourTable
WHERE EName NOT LIKE '%[^,]%';

The pattern will match any characters that aren't a comma, and then the NOT LIKE effectively reverses that; meaning that only rows that contain commas (or a zero length string) will be returned. If you want to omit zero length strings too, then add an additional clause in the WHERE to exclude them.

Answer 2

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 ...."

Answer 3

Please try the following solution.

SQL #1 is tokenizing the ename column by using XML/Query.

Predicate r[text()] picks up tokens with any value.

SQL #2 is much simpler.

SQL #1

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, ename VARCHAR(255));
INSERT INTO @tbl (ename) VALUES
('Peter,J'),
('Mike,S'),
(','),
(', , , ,');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

SELECT * 
FROM @tbl
CROSS APPLY (SELECT CAST('<root><r><![CDATA[' + 
         REPLACE(REPLACE(ename,SPACE(1),''), @separator, ']]></r><r><![CDATA[') + 
         ']]></r></root>' AS XML).query('
         for $x in /root/r[text()]
         return $x
         ').value('.','VARCHAR(255)')) AS t1(c)
WHERE c = '';

Output

+----+---------+---+
| ID |  ename  | c |
+----+---------+---+
|  3 |       , |   |
|  4 | , , , , |   |
+----+---------+---+

SQL #2

SELECT * 
FROM @tbl
    CROSS APPLY (SELECT REPLACE(ename, ',','')) AS t(c)
WHERE c = '';

Output

+----+---------+---+
| ID |  ename  | c |
+----+---------+---+
|  3 |       , |   |
|  4 | , , , , |   |
+----+---------+---+