Split phone field into multiple column fields

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.


more ▼

asked Jan 27, 2011 at 04:04 PM in Default

avatar image

111 7 8 10

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

In Jeff Modens excellent article about Tally or Numbers tables, there's a Split-example that you can use.


more ▼

answered Jan 27, 2011 at 04:09 PM

avatar image

Magnus Ahlkvist
21.1k 19 39 42

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jan 28, 2011 at 05:07 AM

avatar image

Pavel Pawlowski
22.7k 10 15 26

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, 2011 at 08:05 PM Jeff Moden

Looking forward for the article.

Apr 05, 2011 at 09:49 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jan 27, 2011 at 04:13 PM

avatar image

1.1k 1 4 4

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, 2011 at 04:25 PM tlredd68
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jan 27, 2011 at 04:04 PM

Seen: 3221 times

Last Updated: Apr 16, 2012 at 06:13 AM

Copyright 2016 Redgate Software. Privacy Policy