CONCAT_WS and CONCAT help

[zohab]

Hi,

 

I am able to concat 3 fields using following command but if any field is empty or null then I am not getting concate string.

       

        I am getting empty string.

 

SELECT CONCAT(account.phone_country_code,'-',account.phone_city_code,'-',account.phone) AS phone FROM account

 

I try for CONCAT_WS from MYSQL documentation but not able to achibe result.

 

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws

 

Any solution for this problem?

 

-Thanks

[fenway]

SELECT CONCAT_WS('-', account.phone_country_code, account.phone_city_code, account.phone) AS phone FROM account

 

As an added benefit, CONCAT_WS is happy to ignore NULLs -- unlike CONCAT, which barfs.

 

 

[kickstart]

Hi

 

What is the problem you are having with it?

 

SELECT CONCAT_WS('-',account.phone_country_code,account.phone_city_code,account.phone) AS phone 
FROM account

 

Or is it that blank columns are skipped on a row? If so use IFNULL on each column.

 

All the best

 

Keith

[zohab]

Thanks.

 

 

I am getting result but if column in empty then I am getting separator in string like following.

 

----123-123456

 

---123-123456

 

--123-123456

 

It is possible not to include column and separator if column in empty?

 

- Thanks.

 

-123-123456

[kickstart]

Hi

 

Think that is the default behavior for NULL values, which suggests that the columns are blank rather than NULL.

 

All the best

 

Keith