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

Answer

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

COLUMN1	COLUMN2	SOLUTION_1	SOLUTION_2	SOLUTION_3
id01	Jon;karl;lee	3	3	3
id02	Abi;jackson	2	2	2
id03	don	1	1	1