question

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