NUHMAN.com

In SQL, string manipulation is a common task, especially when dealing with user names or displaying data in a specific format. Functions like CONCAT and CONCAT_WS are powerful tools for combining strings. This article explores how to use these functions, particularly in the context of a common LeetCode SQL problem that requires fixing names in a table by capitalizing the first letter and making the rest lowercase.

1. Fix Names in a Table – LeetCode SQL Solution

Original Query:

sql

SELECT 
    user_id,
    CONCAT_WS('',
        UPPER(SUBSTR(name, 1, 1)),
        LOWER(SUBSTR(name, 2))
    ) AS name
FROM
    Users;

Explanation:

This query transforms the name column from the Users table so that:

• The first letter of each name is uppercase.
• The remaining letters are lowercase.

Breakdown:

• user_id: Directly selected from the Users table.
• SUBSTR(name, 1, 1): Extracts the first character of the name.
• UPPER(...): Converts that first character to uppercase.
• SUBSTR(name, 2): Extracts the rest of the name (starting from the second character).
• LOWER(...): Converts the rest of the name to lowercase.
• CONCAT_WS('', ..., ...): Joins both parts without a separator ('' is the empty string).
• AS name: Assigns the formatted name to the output column name.

Example:

If the original name is jOhN, this query will return:

John

2. CONCAT Example

sql

SELECT CONCAT('Hello', ' ', 'World', ' ', 'youtube') AS result;

Explanation:

• CONCAT(...): Joins all the provided strings in order.
• Even though no separator is explicitly mentioned, spaces (' ') are manually included between words.

Output:

Hello World youtube

This is helpful for straightforward string joining where you manually control spacing.

3. CONCAT_WS Example

sql

SELECT CONCAT_WS(' ', 'Hello', 'World', 'youtube') AS result;

Explanation:

• CONCAT_WS(' ', ...): The first argument ' ' is used as a separator between all provided values.
• Unlike CONCAT, CONCAT_WS ignores NULL values entirely (doesn't add a separator for them).

Output:

Hello World youtube

This is a cleaner, more elegant way to join strings with a common separator, especially when some values may be NULL.

Summary: CONCAT vs CONCAT_WS

FeatureCONCATCONCAT_WSSeparatorMust be manually added as argumentsFirst argument is used as a separatorNULL HandlingTreated as empty stringsSkipped completely (no extra separator)Use CaseBasic joining of known stringsStructured joining with consistent separator

Conclusion

Using CONCAT and CONCAT_WS properly can simplify your SQL string operations significantly. Whether you're formatting names in a table or preparing text output, understanding these functions—along with helpers like UPPER, LOWER, and SUBSTR—makes your SQL queries more powerful and readable.

The LeetCode solution for fixing names is a great real-world example of how these tools can be combined for elegant, efficient string transformations in SQL.