This question already has answers here:
SELECT NULLIF(COALESCE(colA,'')+COALESCE(':'+colB,''), '') FROM myTable
COALESCE returns the first not-null argument in its argument list. So the first
COALESCE turns a null
colA into the empty string.
COALESCE first prepends a colon to
colB -- but if
colB is null, attempting to append a string returns NULL! So the result is again the empty string if
colB is null, and a colon plus
colB if it wasn't.
We append the two
COALESCE outputs. We now have everything the OP wanted, except that if both are null, we have the empty string.
NULLIF takes care of that -- if its arguments are equal, it returns NULL, otherwise it returns the first argument.
MySQL combine two columns into one column, If a value does not start with a digit, then the converted value is 0 . So try this: select concat(column1, column2). Two ways to add a space: The following examples show how to use the column names and the SQL concatenation operator for SQL Server and Microsoft Access, MySQL and Oracle. Assuming a table called Customer that has columns called FirstName and LastName, The example below appends the value in the FirstName column with ' ' and then appends the value from the LastName Concatenate SQL Server Columns into a String with CONCAT(), In this tip we look at different ways to concatenate SQL Server string data and how to deal with NULL values when concatenating strings. Overview of SQL Server CONCAT() function To join two or more strings into one, you use the CONCAT() function with the following syntax: CONCAT (input_string1, input_string2 [, input_stringN ]); The CONCAT() takes two up to 255 input strings and joins them into one.
(SELECT cols = CASE WHEN colA IS NOT NULL THEN colA ELSE '' END
+ CASE WHEN colB IS NOT NULL THEN ': '+colB ELSE '' END
SELECT ISNULL(cols, '') from col_data;
MySQL CONCAT() Function, The CONCAT() function adds two or more expressions together. Note: Also look add together. Note: If any of the expressions is a NULL value, it returns NULL Would it be possible to construct SQL to concatenate column values from multiple rows? The following is an example: Table A. PID A B C Table B. PID SEQ Desc A 1 Have A 2 a nice A 3 day. B 1 Nice Work. C 1 Yes C 2 we can C 3 do C 4 this work! Output of the SQL should be -
SQL Server CONCAT Function By Practical Examples, To join two or more strings into one, you use the CONCAT() function with the function to concatenate values in the first_name and last_name columns of the SELECT Countries = STUFF ( (. SELECT ',' + CountryName. FROM Application.Countries. FOR XML PATH ('') ), 1, 1, '') 2. Concatenate Rows Using COALESCE. You can concatenate rows into single string using COALESCE method. This COALESCE method can be used in SQL Server version 2008 and higher. [Solved] How to concatenate two columns in SQL server 2008 , Use the following : Hide Copy Code. select Convert(nvarchar(50),ID)+'-'+Convert(nvarchar(50),Year) as ID_Year from tablename. In MySQL there is a function, GROUP_CONCAT(), which allows you to concatenate the values from multiple rows. Example: SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people FROM users WHERE id IN (1,2,3) GROUP BY a
How to Concatenate Multiple columns in MySQL, You can simply do this programmatically by separately select fields from MySQL Table and store their values in the single variable after concat Concatenating strings depends on the database you are using (you havnt mentioned what version in your question so here goes) In Oracle and DB2 you can use the CONCAT function CONCAT (string, string) SQL Server you can use the '+' operator string1 + string2 + string3.
Comments Please show us your code so far. oops ... thanks @MatBallie Nice solution... I always forget about
CONCAT ... nicely done
instead of hard coding the values mentioned columnNames