Combine two columns into one?

  • 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

  • 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

  • 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)

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

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

    /Kenneth

  • 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

  • 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)

     

     

     

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

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