MySQL concatenating strings

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

MySQL concatenating strings

Post by batfastad »

Hi everyone

I'm trying to take 3 MySQL values, and join them together into 1 string.
I could do this in PHP, but thought I may as well take advantage of the MySQL CONCAT() function. PHP's got enough to do without doing this through 20,000 records at a time ;)

So here's a part of my query:

Code: Select all

SELECT CONCAT(`invoice_prefix`, `invoice_id`, `invoice_suffix`) AS `invoice_no`
However it's not working as I'd like. According to the MySQL manual this will only work if all the strings are NOT NULL. I'd like to be able to just concatenate these fields, regardless of any NULL values.

Is there any way in MySQL to achieve this?

Thanks, B
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL concatenating strings

Post by VladSun »

There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL concatenating strings

Post by batfastad »

That doesn't work as required :(
That just gives me the first character. I'm looking to just join all 3 fields, even if any of them are NULL

Any ideas?
Cheers, B
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL concatenating strings

Post by VladSun »

You could at least post your code ... ;)
[sql]SELECT CONCAT(COALESCE(`invoice_prefixm`, ''), COALESCE(`invoice_id`, ''), COALESCE(`invoice_suffix`, '')) AS `invoice_no`[/sql]
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
batfastad
Forum Contributor
Posts: 433
Joined: Tue Mar 30, 2004 4:24 am
Location: London, UK

Re: MySQL concatenating strings

Post by batfastad »

Ah ok, that works!:lol:
Bit of a beast though, all those nested functions. Might actually revert to PHP handling it again, to keep the MySQL query cleaner. Good to know there's a way though.
Shame there's no simpler way, but I'll definitely think about using that! :lol:

Thanks, B
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: MySQL concatenating strings

Post by VladSun »

It's simple enough :)
Unless you want to use something like this ;)
[sql]SELECT CONCAT(IF(`invoice_prefixm` IS NULL, '', `invoice_prefixm`), ...[/sql]
instead of COALESCE :P
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply