Mysql SELECT AS combine two columns into one

Handling pesky data types and NULL values

Be alert when dealing with incompatible data types. Here's how you can concatenate a numeric and a string column by casting:

-- Number behaving like a string? That's some Transformers' level conversion 
SELECT CONCAT(CAST(`numeric_col` AS CHAR), ' ', `string_col`) AS `combined_col` FROM `table`;


Beware of length overflows to avoid getting cut off:

-- Protect your text from going under the knife. Use LEFT()!
SELECT CONCAT(LEFT(`long_text_col`, 255), ' - ', `short_text_col`) AS `combined_col` FROM `table`;


When it comes to NULLs,CONCAT_WS() skips them while CONCAT() yields NULL if any argument is NULL:

-- CONCAT_WS: It's like having cookies without worrying about the crumbs
SELECT CONCAT_WS(' ', `col1`, `col2`) AS `combined_col` FROM `table`; 

-- CONCAT: When a null appears, the result disappears like Houdini
SELECT CONCAT(`col1`, ' ', `col2`) AS `combined_col` FROM `table`;


Picking an alias like choosing your superhero name

An alias makes it simple to identify our new combined column. Look at this example of using meaningful alias:

-- It isn't Bruce Wayne or Clark Kent - it's their FullNames!
SELECT CONCAT(`FirstName`, ' ', `LastName`) AS `FullName` FROM `Users`;


This can be a lifesaver when exporting data to CSV files (AKA 'The Spreadsheet Gotham').

Wrangling with sweet and sour scenarios

Sometimes, you'll meet two columns that might be NULL, or you may need to work with date and time types:

• If both columns might be NULL, take IFNULL(). It's like getting a guaranteed lucky draw:

-- NULL or not NULL, that's not a question anymore
SELECT IFNULL(CONCAT(`col1`, ' ', `col2`), 'Default Value') AS `combined_col` FROM `table`;


• To mix date and time columns into a single timestamp, use this approach:

-- When date met time, they became 'Timestamp'. Aww!
SELECT CONCAT(`date_col`, ' ', `time_col`) AS `Timestamp` FROM `table`;


• Combining multilingual data? Pay attention to character encoding:

-- Combining Chinese and English like a Kung Fu Panda movie
SELECT CONCAT(_utf8mb4'囍', ' ', `EnglishCol`) AS `combined_col` FROM `table`;
