• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 501
  • Last Modified:

CONCAT a fields if one field is NULL

I would like to concat two fields

select CONCAT(field1, field2)

if field1 or field2 is NULL then i want it treated as a blank string. I've tried CAST, but it doesn't work. As soon as you have a null in a CONCAT it will not work at all, not even partial.

Thanks
0
jaycangel
Asked:
jaycangel
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
select CONCAT(COALESCE(field1,''), COALESCE(field2,''))
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
In sql server

SELECT ISNULL(field1,'')+ISNULL(field2,'')
0
 
awking00Commented:
In Oracle -
select concat(nvl(field1,''),nvl(field2,''))
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
Nick UpsonPrincipal Operations EngineerCommented:
in ingres
select concat(ifnull(field1,''),ifnull(field2,'')) from table

in firebird
select coalesce(field1,'') || coalesce(field2,'') from rdb$database

0
 
jaycangelAuthor Commented:
How can i do it in MySQL?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
my initial suggestion works in mysql
0
 
jaycangelAuthor Commented:
but mysql doesn't have COALESCE ?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
then you have an older version of Mysql...

select CONCAT(IFNULL(field1,''), IFNULL(field2,''))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now