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

|Emp Table|

I tried the below logic

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.

CodePudding user response：

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.

CodePudding user response：

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 | , , , , |   |
 ---- --------- ---