Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010
    Location
    Orlando, FL
    Posts
    311
    Provided Answers: 1

    Question Answered: Difference Between CONCAT or +?

    They appear to do the same thing in the query window but is there a reason to use one over the other? I am aware that CONACT was a new 2012 introduced T-SQL function.

    Code:
    SELECT 'A' + 'B' + 'C' AS PlusExmpl
    
    CONCAT('A', 'B', 'C') AS ConExmpl

  2. Best Answer
    Posted by MCrowley

    "Well, it's actually the other way around.

    when you use + to build up a string, all of the components need to be explicitly convert the items into strings. 'A' + 1 will result in an error. CONCAT ('a' + 1) will result in 'a1'. Binary data in CONCAT will get converted to whatever (probably ASCII, but could be UNICODE) value, and concatenated. Nulls are handled differently by both functions as well. null + anything = null, but CONCAT(null, 'anything') will result in 'anything.

    Here are a few exercises for the interested reader:
    Code:
    --Difference in null handling:
    select concat ('a', 'b', null), 'a' + 'b' + null
    
    -- Difference in data typing
    concat ('a', 1), concat ('a', 0x65)
    
    -- Error thrown by +
    select 'a' + 1
    "


  3. #2
    Join Date
    Jan 2016
    Posts
    24
    Provided Answers: 1
    If you are using concat method then you would only be able to cancat only strings while in case of + operator, you can also concatenate strings with any data type.

  4. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Well, it's actually the other way around.

    when you use + to build up a string, all of the components need to be explicitly convert the items into strings. 'A' + 1 will result in an error. CONCAT ('a' + 1) will result in 'a1'. Binary data in CONCAT will get converted to whatever (probably ASCII, but could be UNICODE) value, and concatenated. Nulls are handled differently by both functions as well. null + anything = null, but CONCAT(null, 'anything') will result in 'anything.

    Here are a few exercises for the interested reader:
    Code:
    --Difference in null handling:
    select concat ('a', 'b', null), 'a' + 'b' + null
    
    -- Difference in data typing
    concat ('a', 1), concat ('a', 0x65)
    
    -- Error thrown by +
    select 'a' + 1
    Last edited by MCrowley; 05-16-16 at 18:04. Reason: Fixing the /code tag

Posting Permissions

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