How to count the number of values in a comma separated (CSV) string in SQL

As the title states, this post demonstrates how to count the number of values in a comma separated string in SQL. The method we use here is to count the number of characters in the string minus the number of characters in the string after all the commas are removed, plus 1. This technique would also work if there are empty values in the CSV string.

On SQL Server you can use something like this:

DECLARE @CSVStr NVARCHAR(50);

SET @CSVStr = N'empid,fname,mane,lname,age,addr,dob,doj';

DECLARE @ValueCount INT;

-- Length of string minus length of string after all commas are removed

SET @ValueCount = LEN(@CSVStr) - LEN(REPLACE(@CSVStr, ',', '')) + 1;

SELECT @ValueCount AS ValCount;

Output:

8

To do the same in MySQL use the following (tested on version 8.0.30-0ubuntu0.20.04.2):

SET @CSVStr = 'empid,fname,mane,lname,age,addr,dob,doj';

-- Length of string minus lengh of string after all commas are removed

SET @ValueCount = CHAR_LENGTH(@CSVStr) - CHAR_LENGTH(REPLACE(@CSVStr, ',',

    '')) + 1;

SELECT @ValueCount AS ValCount;

Output:

8

On a proprietary implementation of SQL like the Aspen SQLplus v14, the following would work:

LOCAL CSVStr CHAR(50);

CSVStr = 'empid,fname,mane,lname,age,addr,dob,doj';

LOCAL ValueCount INT;

-- Length of string minus lengh of string after all commas are removed

ValueCount = CHARACTER_LENGTH(CSVStr) -

             CHARACTER_LENGTH(REPLACE(',' WITH '' IN CSVStr)) + 1;

WRITE ValueCount;

Output:

8