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

what I want to achieve

CodePudding user response：

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.

CodePudding user response：

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;

CodePudding user response：

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;

CodePudding user response：

Here is three solutions using REGEXP_COUNT, SPLIT, ARRAY_SIZE, STRTOK_TO_ARRAY (I would use the REGEXP_COUNT one):

SELECT 
    column1, 
    column2,
    regexp_count(column2, ';') 1 as solution_1,
    ARRAY_SIZE(split(column2, ';')) as solution_2,
    ARRAY_SIZE(strtok_to_array(column2, ';')) as solution_3  
FROM VALUES
    ('id01','Jon;karl;lee'),
    ('id02','Abi;jackson'),
    ('id03','don');

which gives

CodePudding user response：

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