I want to build a query to concatenate a multipla value from database. So this is the query:
SELECT FiscalCode + ' - ' + Name + ' ' + Surname + ' - ' + City + ' - ' BirthTime AS Value, FiscalCode AS OrderBy
FROM AA_V_PHR_CCD_Person
FiscalCode,Name,Surname IS NOT NULL into database configuration but City and BirthTime are NULLABLE into the database configuration.
Now if City or BirthTime is NULL on the db the result of the query is NULL, I can I fix this problem?
Using coalesce to substitute null with an empty string.
SELECT FiscalCode + ' - ' + Name + ' ' + Surname + ' - ' + COALESCE(City,'') + ' - '
COALESCE(BirthTime,'') AS Value, FiscalCode AS OrderBy
FROM AA_V_PHR_CCD_Person
From SQL Server versions 2012 and later, you can use CONCAT which implicitly converts null values to an empty string.
SELECT CONCAT(FiscalCode , ' - ' , Name , ' ' , Surname , ' - ' , City, ' - ', BirthTime) as Value,
FiscalCode AS OrderBy
FROM AA_V_PHR_CCD_Person
You can use ISNULL
SELECT FiscalCode + ' - ' + Name + ' ' + Surname + ' - ' + ISNULL(City,'') + ' - '
ISNULL(BirthTime,'') AS Value, FiscalCode AS OrderBy
FROM AA_V_PHR_CCD_Person
You can do it using ISNULL :
SELECT ISNULL(FiscalCode,'') + ' - ' + ISNULL(Name,'') + ' ' + ISNULL(Surname,'') + ' - ' + ISNULL(City,'') + ' - ' + ISNULL(BirthTime,'') AS Value, FiscalCode AS OrderBy FROM AA_V_PHR_CCD_Person
