I received a file from a client with a phone# field that has multiple phone#'s in this one field. Yes, bad design. Phone# contains: +1 671-333-9876, 676-987-6544, 662-232-1111. I would like to split the phone# data into multiple phone fields based on the number of commas. Every record has the phone#'s separated by commas but there may be 1 phone# up to 10 or more.
I would like to have on the record: Phone#: +1 671-333-9876, 676-987-6544, 662-232-1111 and then the following columns: Phone1 column: +1 671-333-9876 Phone2 column: 676-987-6544 and Phone3 column: 662-232-1111.
asked Jan 27 '11 at 04:04 PM in Default
In Jeff Modens excellent article about Tally or Numbers tables, there's a Split-example that you can use.
answered Jan 27 '11 at 04:09 PM
Been there - Done That - I created a table function that would split an input string on commas. I used it for order numbers so changing it to LTRIM / RTRIM instead of removing the spaces should work. http://ask.sqlservercentral.com/questions/275/dynamic-where-clause-how-can-i-use-a-variable-in-an-in-predicate?page=1#312
answered Jan 27 '11 at 04:13 PM
You can also use a CLR split function for that.
For smaller number of items it doesn't matters whether you use T-SQL tally table Split function or CLR, but for higher number of items, the CLR solution is unbeatable in speed.
You can chek my articles here: Fastest CSV strings splitting using CLR (T-SQL vs. CLR revisited) and SQL Server - String splitting (T-SQL vs. CLR)
Also there is a discussion with Jeff Moden and tests here: http://www.sqlservercentral.com/Forums/Topic943562-338-4.aspx
answered Jan 28 '11 at 05:07 AM