How to split a column into 5 separate columns using T-SQL-CodePudding

Using T-SQL I wanted to split a single column into multiple columns based on a delimiter.

With the given sample data below:

I want to split col1 into 5 columns regardless if there are 5 words or not, sample below:

I was actually able to do this with the use of substring and subquery but I run into the error below. So I'm thinking my method is not the most efficient way:

Msg 40197, Level 20, State 2, Line 44
The service has encountered an error processing your request. Please try again. Error code 8632.

Msg 0, Level 20, State 0, Line 43
A severe error occurred on the current command. The results, if any, should be discarded.

Hoping you could help me. Thank you.

CodePudding user response：

One option is to use JSON to parse the string

Example or dbFiddle

Select  Col1 = JSON_VALUE(S,'$[0]')
       ,Col2 = JSON_VALUE(S,'$[1]')
       ,Col3 = JSON_VALUE(S,'$[2]')
       ,Col4 = JSON_VALUE(S,'$[3]')
       ,Col5 = JSON_VALUE(S,'$[4]')       
 From  YourTable A
 Cross Apply ( values ( '["' replace(string_escape([Col1],'json'),':','","') '"]' ) ) B(S)

Results

Col1        Col2    Col3    Col4    Col5
Japan       Tokyo   Asia    2020    1
Australia   Austria 2022    1       null

CodePudding user response：

I believe something like this using STRING_SPLIT may work here:

SELECT Col1[1]
     , Col1[2]
     , Col1[3]
     , Col1[4]
     , Col1[5]
FROM (
    SELECT STRING_SPLIT(Col1, ':')
    FROM Table
)