CODEWITHSUNDEEP
sqlt-sql
bircastri
bircastri

Reputation: 2161

Query that concatenate possible NULL value

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?

Upvotes: 0

Views: 60

Answers (3)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

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

Upvotes: 2

Pedroklm
Pedroklm

Reputation: 79

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

Upvotes: 0

huMpty duMpty
huMpty duMpty

Reputation: 14460

You can use ISNULL 

SELECT FiscalCode + ' - ' + Name + ' ' + Surname + ' - ' + ISNULL(City,'') + ' - '
ISNULL(BirthTime,'') AS Value, FiscalCode AS OrderBy
FROM AA_V_PHR_CCD_Person

Upvotes: 0

Related Questions