|
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. Thx
(comments are locked)
|
|
In Jeff Modens excellent article about Tally or Numbers tables, there's a Split-example that you can use.
(comments are locked)
|
|
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 I've sussed a fairly major performance problem with the Tally Table breed of splitters. No T-SQL Splitter will be faster than a CLR, but I'm a heck of a lot closer. I'm writing the article as we speak.
Apr 05 '11 at 08:05 PM
Jeff Moden
Looking forward for the article.
Apr 05 '11 at 09:49 PM
Pavel Pawlowski
(comments are locked)
|
|
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 I will give it a shot but is there a way to update the source table with the new fields instead of creating a new table. record is as follows: Seq# 1 URL abc.com addr1 123 main city Tulsa state OK zip 67676 phone# +1 767-987-9876, 454-585-9444 phone1 +1 767-987-9876 phone2 454-585-9444
Jan 27 '11 at 04:25 PM
tlredd68
(comments are locked)
|
