In SQL, combining strings is a common task—especially when formatting names, dates, or custom outputs. Two useful functions for this are CONCAT and CONCAT_WS. Although they both serve to join multiple strings, they behave differently, especially when handling separators and NULL values. Let’s explore their syntax, behavior, and differences with examples.
1. The CONCAT Function
Syntax:
sql
CONCAT(string1, string2, ..., stringN)
Description:
- Joins multiple strings into one.
- If any input is
NULL, it is treated as an empty string. - No separator is added between the values.
Example 1: Simple Concatenation
sql
SELECT CONCAT('Hello', ' ', 'World') AS result;
-- Output: Hello World
Example 2: Concatenation with NULL
sql
SELECT CONCAT('Hello', NULL, 'World') AS result;
-- Output: HelloWorld
As shown above, NULL is treated as an empty string and does not interrupt the concatenation.
2. The CONCAT_WS Function
Syntax:
sql
CONCAT_WS(separator, string1, string2, ..., stringN)
Description:
WS stands for With Separator.- Uses the first argument as a separator between strings.
- Ignores
NULL values altogether (does not insert separator for NULL).
Example 1: Using a Space as a Separator
sql
SELECT CONCAT_WS(' ', 'Hello', 'World') AS result;
-- Output: Hello World
Example 2: Skipping NULL Values Gracefully
sql
SELECT CONCAT_WS(' ', 'Hello', NULL, 'World') AS result;
-- Output: Hello World
Notice how NULL is skipped, and no extra space is added.
Key Differences Between CONCAT and CONCAT_WS
FeatureCONCATCONCAT_WSSeparatorNo separator usedUses the first argument as a separatorNULL HandlingTreats NULL as an empty stringIgnores NULL completelyUse CaseWhen simple joining is neededWhen joining with a custom separator
3. Practical Usage Examples
Full Name Without Space (Not Ideal):
sql
SELECT CONCAT('John', 'Doe') AS fullName;
-- Output: JohnDoe
Full Name With Space (Better Approach):
sql
SELECT CONCAT_WS(' ', 'John', 'Doe') AS fullName;
-- Output: John Doe
Formatted Date with Missing Segment:
sql
SELECT CONCAT_WS('-', '2023', NULL, '06', '02') AS formattedDate;
-- Output: 2023-06-02
This avoids producing something like 2023--06-02, which would happen with CONCAT.
Conclusion
When working with string concatenation in SQL, choosing the right function is key. Use CONCAT when you need straightforward string joining and don’t mind handling NULL values manually. Use CONCAT_WS when you need consistent separators and want to gracefully skip NULL values to avoid awkward formatting.
By understanding the nuances between these two functions, you can produce cleaner and more reliable SQL output in your applications.