I have a table that I save some data include list of numbers.

like this:

I would like to count the number elements in every row in SQL Server

CodePudding user response：

You could use a replacement trick here:

SELECT numbers,
       COALESCE(LEN(numbers) - LEN(REPLACE(numbers, ',', '')), 0) AS num_elements
FROM yourTable;

The above trick works by counting the number of commas (assuming your data really has commas as separators). For example, your last sample data point was:

,31046,36725,42592 => length is 18
310463672542592    => length is 15

Hence the difference in lengths correctly yields the right number of elements.

CodePudding user response：

You may use array_count function to count all the elements in an array

SELECT array_count(numbers) as count;