Split SQL Column value and save count and results

Question

I have a table with column c_name, c_services which got values like this:

 c_name  |      c_services 
---------------------------------------           
       A |   Email,Photos,Phone,Text 
       B |                Phone,Text  
       C |              Email,Photos  
       D |              Photos,Phone

From above table and column (c_services), I am trying to populate data in 5 more columns which are:-

1. Total_Services - based on number of services ex- 4 for c_name 'A'
2. Email - 1 if service present else 0
3. Photos - 1 if service present else 0
4. Phone - 1 if service present else 0
5. Text - 1 if service present else 0

For the new column 'Total_Services', I tried:

array_length(regexp_split_to_table(c_services, ','), 1)  Total_Services
or 
count(regexp_split_to_table(c_services)) Total_Service
or
count(string_split(c_services)) Total_Service

but it is not working. For other column, I am thinking of using a case when to populate 1 or 0 value. Please help.

Are you using MySQL or T-SQL? Please only use the appropriate tag. — Barmar, Feb 26 '20 at 22:11
Never, ever store data as comma separated items. It will only cause you lots of trouble. — jarlh, Feb 26 '20 at 22:17
Please tag your question properly. MySQL and SQL Server are 2 completely different products. — Eric, Feb 26 '20 at 22:18
Corrected the tag. The data source is from salesforce. I am writing an ETL query based job. — sudeep, Feb 26 '20 at 22:20
`SQL` is even worse tag to put. SQL is just a common language used by multiple dbms vendors. Each of them has their variation of syntax. — Eric, Feb 26 '20 at 22:20
Related reading: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/q/3653462/10676716) — GMB, Feb 26 '20 at 22:22
As a good example, the 2 solutions below are useless to you because you don't use MySQL. You just wasted people's time. — Eric, Feb 26 '20 at 22:22
SOQL - Salesforce Object Query Language. Its very similar to SQL — sudeep, Feb 26 '20 at 22:25
@BilboBaggins Thanks for your input. Yes, I am working on it. — sudeep, Feb 26 '20 at 22:48

Radagast · Answer 1 · 2020-05-01T17:42:38.103

Never heard of SOQL before but depending on how predictable and stable c_services column is, you could try this.

select t.*, (t.Email+t.Photos+t.Phone+t.Text) as total_service_count
from (select c_name
            ,case when c_services like '%Email%' then 1 else 0 end as Email
            ,case when c_services like '%Photos%' then 1 else 0 end as Photos
            ,case when c_services like '%Phone%' then 1 else 0 end as Phone
            ,case when c_services like '%Text%' then 1 else 0 end as Text
      from your_table) t;

Split SQL Column value and save count and results

1 Answers1