x

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.

Thx
more ▼

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

tlredd68 gravatar image

tlredd68
111 7 8 8

(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.

[http://www.sqlservercentral.com/articles/T-SQL/62867/][1]

[1]: http://www.sqlservercentral.com/articles/T-SQL/62867/
more ▼

answered Jan 27, 2011 at 04:09 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

(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][3]

[3]: http://www.sqlservercentral.com/Forums/Topic943562-338-4.aspx
more ▼

answered Jan 28, 2011 at 05:07 AM

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

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][1]

[1]: 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

dvroman gravatar image

dvroman
1.1k 2 2

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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1945
x714

asked: Jan 27, 2011 at 04:04 PM

Seen: 2648 times

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