Menu
I use the following SQL to concatenate several database columns from one table into one column in the result set:
SELECT (field1 + '' + field2 + '' + field3) FROM table1
When one of the fields is null I got null result for the whole concatenation expression. How can I overcome this?
The database is MS SQL Server 2008. By the way, is this the best way to concatenate database columns? Is there any standard SQL doing this?
433
To concatenate more than 2 fields with SQL, you can use CONCAT() or CONCAT_WS() function. The syntax is as follows. Let us first see using CONCAT().
Select the same number of columns for each query. Corresponding columns must be the same general data type. Corresponding columns must all either allow null values or not allow null values. If you want to order the columns, specify a column number because the names of the columns you are merging are probably different.
SELECT SOME_OTHER_COLUMN, CONCAT(FIRSTNAME, ',', LASTNAME) AS FIRSTNAME FROM `customer`; Using * means, in your results you want all the columns of the table. In your case * will also include FIRSTNAME . You are then concatenating some columns and using alias of FIRSTNAME .
The SQL standard way of doing this would be:
SELECT COALESCE(field1, '') || COALESCE(field2, '') || COALESCE(field3, '') FROM table1 Example:
INSERT INTO table1 VALUES ('hello', null, 'world'); SELECT COALESCE(field1, '') || COALESCE(field2, '') || COALESCE(field3, '') FROM table1; helloworld If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
