'I am having Issues counting values in a row with separators using SQL

I am new to snowflake and trying the count the number of values in a row with separators using SQL. I am not sure how to go about it. I've googled solutions for this but have not been able to find one.

table name: Lee_tab

user names
id01 Jon;karl;lee;
id02 Abi;jackson;
id03 don;
id04

what I want to achieve

user names name_count
id01 Jon;karl;lee; 3
id02 Abi;jackson; 2
id03 don; 1
id04 0
sqlsnowflake-cloud-data-platform


Solution 1:[1]

It depends on which DataBase you're using, because there are some different things in syntax. I made your example with using SQLite Browser and I have a result like this one:

SELECT SUM(length(names) - length(replace(names, ';', '')) +1) AS TotalCount FROM Lee_tab where id = USER ID

As I know, in Postgres there's no length, it's just len there, so, pay an attention.

My query-it's just a formula to how count values, separated by ; To get your result, you should learn how to join.

Solution 2:[2]

Here is a different answer, using the Snowflake SPLIT_TO_TABLE function. This function splits the string on the delimiter, creating a row for each value, which we lateral join back to the CTE table, finally we COUNT and GROUP BY using standard SQL syntax:

with cte as (
    select 'id01' as user, 'Jon;karl;lee' as names union all
    select 'id02' as user, 'Abi;jackson' as names union all
    select 'id03' as user, 'don' as names
)
select user, names, count(value) as count_names
    from cte, lateral split_to_table(cte.names, ';')
group by user, names;

Solution 3:[3]

Rewriting json_stattham's answer using Snowflake syntax. Basically, we are just counting the number of separators (semicolons) in the string and adding 1. There is no need to use the SUM() function as in json_stattham's answer.

with cte as (
    select 'id01' as user, 'Jon;karl;lee' as names union all
    select 'id02' as user, 'Abi;jackson' as names union all
    select 'id03' as user, 'don' as names
)
SELECT user, names, (length(names) - length(replace(names, ';'))) + 1 AS name_count 
FROM cte;

Solution 4:[4]

This is the answer for your query

select user,names,(len(names) - len(replace(names, ';',''))+1) names_count from Lee_tab;

for more understanding check this ,i have done all https://www.db-fiddle.com/f/BQuEjw2pthMDb1z8NTdHv/0

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 json_stattham
Solution 2 Maja F.
Solution 3
Solution 4 Simeon Pilgrim

Related Questions