Mysql concatenation operator

Mastering concatenation in MySQL: A deeper dive

When pipes aren't just pipes: setting sql_mode

Some SQL dialects use || for string concatenation, but MySQL sees it as logical OR. The SQL mode PIPES_AS_CONCAT can change this:

SET sql_mode='PIPES_AS_CONCAT';
SELECT 'MySQL' || ' ' || 'Rocks!' AS pep_talk;


This swaps MySQL's thinking cap for an ANSI standard one, and || acts for string connection like Lego bricks.

Mixing and matching data types

Though its name implies stringiness, CONCAT() can handle different data types, by automatically converting them into a lovely text string:

SELECT CONCAT('Price: $', CAST(100 AS CHAR));


This transforms the integer 100 into string '100', and combines both, giving us a price tag: 'Price: $100'. You're now a retailer (kinda 😇)!

Null Beware! Concat's hidden pitfalls

CONCAT() sees a NULL value as a party pooper, returning NULL. Luckily, CONCAT_WS() is more forgiving, skipping NULL values:

SELECT CONCAT_WS(' ', 'First', NULL, 'Last');


This gets us 'First Last'. The NULL has left the building!

Maintain peak Performance

Proper usage of CONCAT() will keep your SQL running faster than Usain Bolt. Overuse, especially in loops or huge queries, may slow it down more than a sleepy sloth. Be wary!

Proper Alias: Be a SQL superhero

Adding an alias to your CONCAT() output is like putting a label -- it makes your SQL results easier to comprehend and debug:

SELECT CONCAT(firstName, ' ', lastName) AS fullName FROM users;


This part just makes it clear that fullName is the splendid union of firstName and lastName!

Navigating SQL dialects: Beware of differences

Worlds collide when SQL syntax differs across platforms. For instance, MS SQL Server laughs at || for string concatenation, using the + operator instead, like a mathematical rebel. So, remember to use the desired syntax based on your SQL playground!