SQL trying to separate entries values in a database field

Question

I'm trying to write some SQL code that will allow me to separate the individual values from this field in a database feed. I have tried a few different things and none are working properly and while I can get it to work via VBA or program it via another language I was wondering if there was a way to do it via SQL directly (with or without having to use a stored procedure). Ideally I would like to be able to populate a table with the unique values only which would require having to check the values already stored in the destination table before inserting the new records however just being able to separate the values into individual entries would be good for now.

SELECT CASE
           WHEN CHARINDEX(',', vaccines) > 0
           THEN SUBSTRING(vaccines, 1, LEN(vaccines) - CHARINDEX(',', REVERSE(vaccines)))
           ELSE ''
       END AS vax1,
        CASE
           WHEN CHARINDEX(',', vaccines) > 0
           THEN REVERSE(SUBSTRING(REVERSE(vaccines), 
                       1, 
                       CHARINDEX(',', REVERSE(vaccines)) - 1))
        
           ELSE vaccines
       END AS vax2
FROM(SELECT DISTINCT vaccines FROM Covid_Vaccination_By_Country) AS Vax(vaccines);

However while this is splitting the first entry away reasonably well its not doing anything for subsequent values.

I was trying to come up with the simplest and smallest block of SQL to do this but no luck so far any ideas?

vaccine
Covaxin, Oxford/AstraZeneca
EpiVacCorona, Sputnik V
Johnson&Johnson
Johnson&Johnson, Moderna, Pfizer/BioNTech
Moderna, Oxford/AstraZeneca, Pfizer/BioNTech
Moderna, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing, Sputnik V
Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing, Sinopharm/Wuhan, Sputnik V
Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing, Sputnik V
Oxford/AstraZeneca, Pfizer/BioNTech, Sputnik V
Pfizer/BioNTech
Pfizer/BioNTech, Sinopharm/Beijing
Sinopharm/Beijing
Sinopharm/Beijing, Sinopharm/Wuhan, Sinovac
Sinovac
Sputnik V

It seems you were looking to parse into columns. If you have a known or maximum number of observations... take a peek at https://dbfiddle.uk/?rdbms=sqlserver_2016&fiddle=cc68530265127465ddb20fe3fccb3211 — John Cappelletti, Mar 28 '21 at 17:37
Sorry I meant to mention that I was looking at SQL Server 18 — user2711259, Mar 29 '21 at 09:58

score 0 · Answer 1 · edited Mar 28 '21 at 17:36

For SQL Server 2016 and higher, you can use string_split() to get what you want. To remove spaces I have used ltrim(rtrim(vaccine)). If you are using SQL Server 2017 and higher then you can use trim(vaccine) instead.

Schema:

 CREATE TABLE Covid_Vaccination_By_Country (vaccineS VARCHAR(500));

 INSERT INTO Covid_Vaccination_By_Country VALUES('Covaxin, Oxford/AstraZeneca');
 INSERT INTO Covid_Vaccination_By_Country VALUES('EpiVacCorona, Sputnik V');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Johnson&Johnson');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Johnson&Johnson, Moderna, Pfizer/BioNTech');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Moderna, Oxford/AstraZeneca, Pfizer/BioNTech');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Moderna, Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing, Sputnik V');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing, Sinopharm/Wuhan, Sputnik V');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Oxford/AstraZeneca, Pfizer/BioNTech, Sinopharm/Beijing, Sputnik V');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Oxford/AstraZeneca, Pfizer/BioNTech, Sputnik V');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Pfizer/BioNTech');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Pfizer/BioNTech, Sinopharm/Beijing');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Sinopharm/Beijing');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Sinopharm/Beijing, Sinopharm/Wuhan, Sinovac');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Sinovac');
 INSERT INTO Covid_Vaccination_By_Country VALUES('Sputnik V');

Query:

 SELECT
     LTRIM(RTRIM((value))) vaccine
 FROM
     (SELECT DISTINCT vaccines 
      FROM Covid_Vaccination_By_Country) AS Vax(vaccines)
 CROSS APPLY
     STRING_SPLIT (vaccines, ',')
 GO

Output:

vaccine
Covaxin
Oxford/AstraZeneca
EpiVacCorona
Sputnik V
Johnson&Johnson
Johnson&Johnson
Moderna
Pfizer/BioNTech
Moderna
Oxford/AstraZeneca
Pfizer/BioNTech
Moderna
Oxford/AstraZeneca
Pfizer/BioNTech
Sinopharm/Beijing
Sputnik V
Oxford/AstraZeneca
Pfizer/BioNTech
Sinopharm/Beijing
Sinopharm/Wuhan
Sputnik V
Oxford/AstraZeneca
Pfizer/BioNTech
Sinopharm/Beijing
Sputnik V
Oxford/AstraZeneca
Pfizer/BioNTech
Sputnik V
Pfizer/BioNTech
Pfizer/BioNTech
Sinopharm/Beijing
Sinopharm/Beijing
Sinopharm/Beijing
Sinopharm/Wuhan
Sinovac
Sinovac
Sputnik V

db<fiddle here

Thanks folks that works fine I had looked at String Split but never considered Cross Apply — user2711259, Mar 29 '21 at 09:57
You are most welcome @user2711259. Happy to help. Best wishes. — Kazi Mohammad Ali Nur Romel, Mar 29 '21 at 10:01

SQL trying to separate entries values in a database field

1 Answers1