How to count all elements in an array in sql server-CodePudding

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

like this:

numbers

NULL

،42593

،42593،42594،36725،42592،36725،42592

،42593،42594،36725،42592

،31046،36725،42592

I would like to count the number elements in every row in Sqlserver

count

0

1

6

4

3

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;