Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2006
    Posts
    26

    Unanswered: Concatenate two fields with - between the values.

    Good day all,
    I have a field in a table which i'm trying to update to using two values from two seperate fields from the same table but adding a dash (-) inbetween the values, so the end result should look something like this Value1-Value2.
    The Code i'm using is as below:-

    use MyDB
    update tbMyTable
    set Field03= Field01 + "-" + Field02
    where Field03 is null

    but using this I get the error message "Invalid column name '-'."
    Can anyone point me in the right direction? I can't quite figure this out.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    change doublequotes to singlequotes

    doublequotes are used to delimit identifiers, singlequotes are used to delimit character strings

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2006
    Posts
    26
    Cheers R937.

    I gave that a go but i'm getting this error message.

    "Server: Msg 8115, Level 16, State 6, Line 2
    Arithmetic overflow error converting varchar to data type numeric.
    The statement has been terminated."

    I did try changing the Destination Field Data type to Binary, Numeric, varchar etc, but nothing seems to work. The field should be able to hold at least 8 characters.

    Thanks in advance,
    Vega....

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    obviously, based on the error message, Field01 is numeric

    so you'll have to CAST it

    .. SET Field03= CAST(Field01 AS VARCHAR) + "-" + Field02
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2006
    Posts
    26
    Thanks r937.
    That worked perfectly !!

    Cheers again.

  6. #6
    Join Date
    Dec 2008
    Posts
    135
    Hi vegala,
    while Concatenate the varchar fields use isnull or coalesce to avoid null values

  7. #7
    Join Date
    Sep 2006
    Posts
    26
    Aah right.
    Thanks for the tip, i'll bear that in mind.
    I find I am using SQL server (2000 ed) more these days as I move more of the work away from my familiar ground of Access to SQL so the help I get from here is greatly appreciated.
    Thanks again.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh, and use Coalesce() and not isnull
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •