CODEWITHSUNDEEP
sqlsql-servert-sql
Kellyanne Connoway
Kellyanne Connoway

Reputation: 11

Why is string concatenation in my SELECT statement not working?

I have a query of the form

SELECT '(''' + 
       SomeVarCharColumn + 
       ''',' + 
       CONVERT(NVARCHAR(MAX), SomeIntColumn) + 
       ',' + 
       CONVERT(NVARCHAR(MAX), SomeOtherIntColumn) 
       + ')' 
FROM SomeTable

and all the results are NULL. Any idea where I'm going wrong?

Upvotes: 1

Views: 75

Answers (3)

Anthony Hancock
Anthony Hancock

Reputation: 931

Use the CONCAT() function. When null values are encountered they are simply omitted from the resulting string. Additionally, int to char conversions are implicit no need for the convert, you can find a chart of implicit conversions here https://msdn.microsoft.com/en-us/library/ms187928.aspx

SELECT 
    CONCAT('(''',SomeVarCharColumn,''',',SomeIntColumn,',',SomeOtherIntColumn,')') AS NewVarchar
FROM SomeTable

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If any column is NULL, then the value is NULL. Use COALESCE():

SELECT '(''' + COALESCE(SomeVarCharColumn, '') + ''',' +
       COALESCE(CONVERT(NVARCHAR(MAX), SomeIntColumn), '') + ',' + 
       COALESCE(CONVERT(NVARCHAR(MAX), SomeOtherIntColumn), '') + ')'
FROM SomeTable

Upvotes: 2

SqlZim
SqlZim

Reputation: 38023

null + 1 is null
null + 'things' is null

try this instead: 

select '('''
    + isnull(somevarcharcolumn,'')
    + ''','
    + isnull(convert(nvarchar(max), someintcolumn),'null')
    + ','
    + isnull(convert(nvarchar(max), someotherintcolumn),'null')
    + ')' 
from sometable

Upvotes: 2

Related Questions