-1

I have tried combining columns using select (column1 + ',' + column2 + ',' + column3) as column4 from table. But i didn't really get the result for three columns. It had worked for two columns. when i try to add one more its not retrieving any information from the table.Please help me in this.

GEEK
  • 57
  • 8
  • Are you getting an error message or a result that doesn't meet your expectation? Please copy and paste, don't retype, your *exact* SQL statement into your question. From what you have above, looks like you're missing `+` before `column3` – Bob Kaufman Feb 23 '16 at 19:27
  • If one of them is null, all will be null. You can use coalesce for these scenarios. – Silvermind Feb 23 '16 at 19:28
  • In addition to not handling nulls - if the SQL command you're attempting to execute contains exactly the same syntax as the fragment included in your question, then it's missing a plus sign (between the last ',' and column3). – SWalters Feb 23 '16 at 19:28
  • yeah! #Silvermind, that's exactly the thing with me. Can you provide me with some sql that over comes it. I didn't put + here. – GEEK Feb 23 '16 at 19:32
  • 1
    Possible duplicate of [SQL Server String Concatenation with Null](http://stackoverflow.com/questions/2916791/sql-server-string-concatenation-with-null) – Tab Alleman Feb 23 '16 at 19:41
  • i think its a better solution. I will use it Thanks Tab Alleman – GEEK Feb 23 '16 at 19:45

2 Answers2

2

If one of the columns have NULL as value, the result will be null, so you can use the coalesce or the isnull. The ISNULL function and the COALESCE expression have a similar purpose but can behave differently.

  • Because ISNULL is a function, it is evaluated only once. As described above, the input values for the COALESCE expression can be evaluated multiple times.

  • Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.

  • The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one) whereas COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1) although equivalent have different nullability values. This makes a difference if you are using these expressions in computed columns.

So the result will be something like:

SELECT isnull(column1, 'abs') + ',' + isnull(column2, 'abc') + ',' + isnull(column3, 'abc') as OneColumnResult from TABLE

Hope it helps.

victorayub
  • 59
  • 6
0

You're missing the last '+' in this snippet. Should be

select (column1 + ',' + column2 + ',' + column3) as column4

Surprised you're not getting an error

TG01
  • 195
  • 2
  • 9