Page 1 of 1

MySQL concatenating strings

Posted: Mon Oct 20, 2008 10:49 am
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

Re: MySQL concatenating strings

Posted: Mon Oct 20, 2008 11:33 am
by VladSun

Re: MySQL concatenating strings

Posted: Mon Oct 20, 2008 11:58 am
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

Re: MySQL concatenating strings

Posted: Mon Oct 20, 2008 12:14 pm
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]

Re: MySQL concatenating strings

Posted: Mon Oct 20, 2008 1:16 pm
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

Re: MySQL concatenating strings

Posted: Mon Oct 20, 2008 1:59 pm
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