String Functions in SQL: CONCAT, LENGTH, REPLACE and More

Table of Contents

1. Introduction
2. Why String Functions Matter
3. Overview of SQL String Functions
4. CONCAT() – Combine Strings
5. LENGTH() – Get String Length
6. CHAR_LENGTH() vs LENGTH()
7. REPLACE() – Substitute Substrings
8. UPPER() and LOWER() – Case Conversion
9. TRIM() – Remove Leading/Trailing Spaces
10. LTRIM() and RTRIM() – Left/Right Trimming
11. SUBSTRING() – Extract Part of a String
12. LEFT() and RIGHT() – Characters from Edges
13. INSTR() or POSITION() – Find Substring Location
14. LOCATE() in MySQL
15. REPEAT() – Repeat Strings
16. CONCAT_WS() – Concatenate with Separator
17. Real-World Example: Formatting Names
18. Real-World Example: Cleaning Phone Numbers
19. Best Practices for String Manipulation
20. Summary and What’s Next

1. Introduction

Strings (text data) are everywhere in databases — names, addresses, product codes, emails, etc. SQL provides string functions to manipulate, analyze, and transform this data effectively for reporting, cleaning, and business logic.

2. Why String Functions Matter

String functions let you:

• Combine fields (like first + last name)
• Format and clean data
• Extract or substitute text
• Prepare strings for display or comparison
• Enable fuzzy matching and reporting

3. Overview of SQL String Functions

Here are some of the most commonly used string functions:

4. CONCAT() – Combine Strings

sqlCopyEditSELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

You can also combine more than two strings:

sqlCopyEditSELECT CONCAT(city, ', ', state, ' - ', zip_code) AS full_address;

5. LENGTH() – Get String Length

sqlCopyEditSELECT LENGTH('SQL Rocks'); -- Returns 9 (bytes)

In some systems, LENGTH() counts bytes, not characters. For multi-byte encodings like UTF-8, use CHAR_LENGTH() instead.

6. CHAR_LENGTH() vs LENGTH()

Example:

sqlCopyEditSELECT CHAR_LENGTH('नमस्ते'), LENGTH('नमस्ते');

7. REPLACE() – Substitute Substrings

sqlCopyEditSELECT REPLACE('Welcome to 2023', '2023', '2024');
-- Output: Welcome to 2024

Can also be used for cleaning data (e.g., removing dashes in phone numbers).

8. UPPER() and LOWER() – Case Conversion

sqlCopyEditSELECT UPPER(name), LOWER(email)
FROM users;

Useful for standardizing text for comparison or display.

9. TRIM() – Remove Leading/Trailing Spaces

sqlCopyEditSELECT TRIM('   Hello World   '); -- Outputs: 'Hello World'

Helps clean up user-entered data.

10. LTRIM() and RTRIM() – Trim Sides

sqlCopyEditSELECT LTRIM('   SQL');  -- Output: 'SQL'
SELECT RTRIM('SQL   ');  -- Output: 'SQL'

Trim only from the left or right.

11. SUBSTRING() – Extract Part of a String

sqlCopyEditSELECT SUBSTRING('PostgreSQL', 1, 4); -- Output: 'Post'

In MySQL: SUBSTRING(string, position, length)
In SQL Server: SUBSTRING(string, start, length)

12. LEFT() and RIGHT() – Characters from Edges

sqlCopyEditSELECT LEFT('Database', 4);  -- Output: 'Data'
SELECT RIGHT('Database', 4); -- Output: 'base'

Quickly grab a fixed number of characters from either side.

13. INSTR() or POSITION() – Find Substring Location

sqlCopyEditSELECT INSTR('SQL Tutorial', 'Tutorial'); -- Output: 5

Returns position of substring. Returns 0 if not found.

14. LOCATE() in MySQL

sqlCopyEditSELECT LOCATE('lo', 'Hello World'); -- Output: 4

LOCATE(substr, str) is MySQL’s alternative to INSTR().

15. REPEAT() – Repeat Strings

sqlCopyEditSELECT REPEAT('*', 10); -- Output: '**********'

Useful for creating placeholders, testing, or formatting.

16. CONCAT_WS() – Concatenate with Separator

sqlCopyEditSELECT CONCAT_WS('-', '2024', '05', '12'); -- Output: '2024-05-12'

WS = “With Separator”. More elegant than CONCAT() + manual separators.

17. Real-World Example: Formatting Names

sqlCopyEditSELECT CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2))) AS formatted_name
FROM users;

Capitalizes first letter, lowercase the rest — e.g., ‘john’ → ‘John’.

18. Real-World Example: Cleaning Phone Numbers

sqlCopyEditSELECT REPLACE(REPLACE(phone, '-', ''), ' ', '') AS cleaned_phone
FROM contacts;

Removes dashes and spaces for standardization.

19. Best Practices for String Manipulation

• Use CHAR_LENGTH() when working with multilingual data
• Always sanitize input before storing in string columns
• Normalize text case using LOWER() or UPPER() for comparison
• Avoid excessive nested string operations — readability matters
• Consider using stored procedures or views for complex logic

20. Summary and What’s Next

SQL string functions are powerful tools for cleaning, transforming, and presenting text data. Whether you’re formatting names, building display strings, or standardizing input, mastering these functions will help you handle textual data more effectively.