question

Sunshine15 avatar image
Sunshine15 asked

How to split a column value to two column values?

I have the Numbers column like this: Numbers 305-58-670 0 01-421-0402-00 234-57 and 26-739 3782 60 & 1034-63 R2309434, R2844994 I want to separate the Numbers column into two columns in SQL. Numbers Number1 Number2 305-58-670 0 305-58-670 0 01-421-0402-00 01-421-0402-00 234-57 and 26-739 234-57 26-739 3782 60 & 1034-63 3782 60 1034-63 R2309434, R2844994 R2309434 R2844994 Could anyone help me out?
t-sqlsql-server-2012
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

@Sunshine15 This could be pretty easy but there is a small problem with data. It looks like you are looking to split the single value into 2 based on the following delimiters: " " or " and " or " & " or ", ". The problem is that in row 4 you have the space between numbers 3782 and 60 but you would like it to still remain as the same number even though the space is the valid delimiter for the number in row 1 (305-58-670 0) which needs to be delimited based on space.
2 Likes 2 ·

1 Answer

·
Oleg avatar image
Oleg answered
The script in this solution works as per data in the original question. If the data has some additional twists then the script might have to be modified. The logic for the script is as follows: - The data is assumed to include multiple delimiters which could be space, ' and ', ' & ' or ', '. All such delimiters are replaced with single space so that the position of the space can be used to determine how to split the value into 2 values. - Because it is possible that there is a space in the value which is not a delimiter, i.e. the value **3782 60 & 1034-63** is requested to be split into **3782 60** / **1034-63**, the position of ***last space*** of the cleaned value is used for splitting. This is why the cleaned value is reversed prior to examining the position of the space (the position of the last space then comes in first). - If the value does not have a delimiter then it is assumed that it should not be split, so Number1 column then gets the entire value and Number2 column gets blank Here is the script which works based on the data in question (it assumes that there is a table called SomeTable with the column named Numbers): ; with records as ( select Numbers, replace(replace(replace(Numbers, ' and ', ' '), ' & ', ' '), ', ', ' ') CleanedNumbers, charindex(' ', reverse(replace(replace(replace(Numbers, ' and ', ' '), ' & ', ' '), ', ', ' '))) Pos from SomeTable ) select Numbers, substring(CleanedNumbers, 1, len(CleanedNumbers) - Pos) Number1, case when Pos = 0 then '' else substring(CleanedNumbers, len(CleanedNumbers) - Pos + 2, 50) end Number2 from records; Based on the data in question, the script produces the following output: Numbers Number1 Number2 -------------------- -------------------- -------------------- 305-58-670 0 305-58-670 0 01-421-0402-00 01-421-0402-00 234-57 and 26-739 234-57 26-739 3782 60 & 1034-63 3782 60 1034-63 R2309434, R2844994 R2309434 R2844994 Hope this helps. Oleg
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you very much.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.