Combine two columns into one?

Question

Combine two columns into one?

Ron-279530

Ten Centuries

Points: 1381
More actions
August 15, 2006 at 6:01 pm

#367167

I have an area code column and a phone number column that I would like to "merge" into one column for better management and reporting? This seems like it should be simple enough but can't seem to find any trick to doing this. Any suggestions?
Thanks
Ron
Edit..This is in SQL Server 2000

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Andrew Lias SSC Enthusiast Points: 154 More actions · Answer 1

What have you tried and what difficulties have you run into?

It would seem that all you need to do is add a column to the table (e.g., AreaPhone), then run an update that concatenates the data from the orignal columns into the new columns. Something like

update PhoneTable

Set AreaPhone = AreaCode + PhoneNumber

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Having been in the telecommunications world for about 11 years, lemme just say...

DON'T COMBINE THE NPA AND NXX COLUMNS!!!!!!!! You will hate yourself so bad in the future for more reasons than I have the room to post on this forum. Also, don't combine the NPA with the 7 digit NXX/LINE column combination.

Instead, create a calculated column with something like the formula that Andrew used...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron-279530 Ten Centuries Points: 1381 More actions · Answer 3

I should have been more clear, I do want a third column that is the combination of the two. Thanks for the suggestions!

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 4

Then do as Jeff says, and make that column a computed column.

/Kenneth

Ron-279530 Ten Centuries Points: 1381 More actions · Answer 5

Andrew,

This literally added the columns (ie mathmatically) instead of concatenating them.

I have tried the following:

update PhoneTable

Set AreaPhone = (AreaCode & PhoneNumber)

with no luck either.

Other suggestions?

Thanks

Gopi Muluka SSCarpal Tunnel Points: 4594 More actions · Answer 6

Ron,

I think the two columns (AreaCode & PhoneNumber) are of type int, thats why concatenation is not happening

try this

update

PhoneTable

set

AreaPhone =rtrim(convert(char,AreaCode))+convert(char,PhoneNumber)