Post reply

adding 3 columns into a new column in an existing table

  • November 5, 2007 at 2:47 am

    #183559

    I have an existing table where i have add1, add2, add3 as columns. Now i want to add all the 3 into one column, Address. The problem is if any one column is NULL, then the resultant row also becomes null.

    Also, i want to introduce the [Enter] after add1 and add2 so that when i retrieve the Address, formatting looks good.

    What i tried is set the formula for the Address column as [add1]+[add2]+[add3], but this gives Null when there is a null in any one of the column.

    Can some one help me please?

    kesk

  • November 5, 2007 at 3:04 am

    #748149

    try like this:

    selectisnull (add1, '') + isnull (add2, '') +isnull (add3, '') as 'Address' from

    🙂

  • November 5, 2007 at 3:21 am

    #748154

    Use CHAR(10) to add newline character...

    --Ramesh

  • November 5, 2007 at 3:57 am

    #748165

    Thanks for the replies. It works on a query.

    But i want the Address column in the table, not as a query result. Once the Address column is correctly stored in the table, i want to delete the add1, add2 and add3 columns permenantly.

    I tried this statement in the SQL Query Analyser window, the syntax checks ok, but execution returns error msg 208, level 16.

    Invalid object name 'Address'.

    code:

    Insert into Address select isnull (address1, '') + isnull (address2, '') + isnull (address3, '') from Customers

  • November 5, 2007 at 4:47 am

    #748174

    1. Add the required column Address to the table

    ALTER TABLE Customers ADD Address VARCHAR(1000) NULL

    2. Run the update query

    UPDATE Customers SET Address = COALESCE(add1, '') + COALESCE(add2, '') + COALESCE(add3, '')

    3. Drop the columns

    ALTER TABLE DROP COLUMN add1, add2, add3

    4. Make Address column non-nullable

    ALTER TABLE Customers ADD Address VARCHAR(1000) NOT NULL

    --Ramesh

  • December 28, 2007 at 4:03 am

    #763210

    Kishore.P (11/5/2007)

    try like this:

    selectisnull (add1, '') + isnull (add2, '') +isnull (add3, '') as 'Address' from

    🙂

    1 You can also use Coalesce() in place of isnull

    2 Dont single quote the column alias name. If you want readablity always use [col_name]

    Madhivanan

    Failing to plan is Planning to fail

  • December 28, 2007 at 1:23 pm

    #763404

    This whole thread and the original concept isn't something I would even consider doing... First, there may be a GUI out there that you don't know about that requires those columns. You will break their code if there is.

    Second... you should never combine data that's been nicely split out if you don't have to. It's kinda like combining parts of a name into a single FullName column and deleting the original columns... it's a form of "Death by SQL".

    Third, this is easy... instead of creating an Address column, populating it, and deleting the original columns (almost always a horrible idea), just create a Calculated column as Address...

    ... Read about "Calculated" columns in Books Online.

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

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

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