SQL String Functions0%

SQL String Functions

Beginner12 min read•Updated: 2026-09-12

SQL String Functions: Manipulation, Extraction, and Formatting

Raw text stored in database tables is rarely in the exact display format required by end-user interfaces. Names need capitalizing, full names need stitching together from first and last names, email domains need extracting, and messy user input containing trailing spaces needs sanitizing. SQL provides a rich library of built-in Scalar String Functions.

1. String Concatenation: `CONCAT()` and `CONCAT_WS()`

In standard SQL (and unlike Python or JavaScript), you cannot use the plus operator (+) to concatenate strings (in MySQL, 'A' + 'B' converts both to 0 and yields 0!). You must use concatenation functions:

`CONCAT(str1, str2, ...)`

Joins strings together sequentially. Crucial Rule: If any argument is NULL, CONCAT() returns NULL!

1SELECT CONCAT(first_name, ' ', last_name) AS full_name
2FROM employees;

`CONCAT_WS(separator, str1, str2, ...)` (Concatenate With Separator)

The first argument is the delimiter. Unlike CONCAT(), CONCAT_WS skips NULL values automatically!

1-- Build full mailing address, skipping missing apartment numbers cleanly:
2SELECT CONCAT_WS(', ', street_address, apartment_no, city, postal_code) AS formatted_address
3FROM customer_addresses;

2. Case Conversion: `UPPER()` and `LOWER()`

1SELECT 
2    UPPER(full_name) AS loud_name,     -- 'AARAV SHARMA'
3    LOWER(email) AS standardized_email -- 'aarav.sharma@example.com'
4FROM customers;

3. String Length: `LENGTH()` vs `CHAR_LENGTH()`

Understanding the difference between byte length and character length is critical when dealing with multilingual Unicode text:

1-- English ASCII:
2SELECT LENGTH('MySQL'), CHAR_LENGTH('MySQL');
3-- Result: Both return 5.
4 
5-- Multilingual Hindi / Emojis:
6SELECT LENGTH('भारत'), CHAR_LENGTH('भारत');
7-- Result: LENGTH = 12 bytes! CHAR_LENGTH = 4 characters!

LENGTH(str): Returns length in bytes.
CHAR_LENGTH(str): Returns length in characters. Always use CHAR_LENGTH when measuring text length for user validation!

4. Substring Extraction: `SUBSTRING()` / `SUBSTR()`

Extracts a slice of text starting at a specific position. Note: SQL indexes strings starting at 1, NOT 0!

1-- Syntax: SUBSTRING(string, start_position, [length])
2 
3-- Extract first 3 characters (e.g. Area Code):
4SELECT SUBSTRING(phone_number, 1, 3) FROM contacts;
5 
6-- Extract from position 5 to the end:
7SELECT SUBSTRING('MSK-TECH-ACADEMY', 5); -- 'TECH-ACADEMY'
8 
9-- Extract domain from email using SUBSTRING and LOCATE/INSTR:
10SELECT 
11    email,
12    SUBSTRING(email, INSTR(email, '@') + 1) AS email_domain
13FROM users;

5. Cleaning Text: `TRIM()`, `LTRIM()`, and `RTRIM()`

Removes unwanted leading and trailing whitespace characters:

1-- Sanitize user input:
2SELECT TRIM('   clean text   ') AS cleaned; -- 'clean text'
3 
4-- Trim specific characters:
5SELECT TRIM(BOTH '#' FROM '###Special Offer###'); -- 'Special Offer'

6. String Replacement: `REPLACE()`

Replaces all occurrences of a search string with a replacement string:

1-- Update old domain references in stored URLs:
2SELECT REPLACE('https://old-domain.com/page1', 'old-domain.com', 'mskinstitute.in') AS new_url;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In SQL string indexing (e.g., in the SUBSTRING() function), what is the index position of the first character?

Question 2

What does CONCAT('Hello', NULL, 'World') return in MySQL?

Question 3

Which string concatenation function automatically uses a specified delimiter and skips any NULL arguments?

Question 4

Which function measures the human-perceived count of characters rather than the physical byte count of a Unicode string?

Question 5

What is the output of SELECT SUBSTRING('DATABASE', 5, 4);?

Next Lesson

Numeric & Math Functions

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Pagination with LIMIT and OFFSET	Numeric & Math Functions

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Numeric & Math Functions

SQL String Functions

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

SQL String Functions: Manipulation, Extraction, and Formatting

1. String Concatenation: `CONCAT()` and `CONCAT_WS()`

`CONCAT(str1, str2, ...)`

Joins strings together sequentially. Crucial Rule: If any argument is NULL, CONCAT() returns NULL!

1SELECT CONCAT(first_name, ' ', last_name) AS full_name
2FROM employees;

`CONCAT_WS(separator, str1, str2, ...)` (Concatenate With Separator)

The first argument is the delimiter. Unlike CONCAT(), CONCAT_WS skips NULL values automatically!

1-- Build full mailing address, skipping missing apartment numbers cleanly:
2SELECT CONCAT_WS(', ', street_address, apartment_no, city, postal_code) AS formatted_address
3FROM customer_addresses;

2. Case Conversion: `UPPER()` and `LOWER()`

1SELECT 
2    UPPER(full_name) AS loud_name,     -- 'AARAV SHARMA'
3    LOWER(email) AS standardized_email -- 'aarav.sharma@example.com'
4FROM customers;

3. String Length: `LENGTH()` vs `CHAR_LENGTH()`

Understanding the difference between byte length and character length is critical when dealing with multilingual Unicode text:

1-- English ASCII:
2SELECT LENGTH('MySQL'), CHAR_LENGTH('MySQL');
3-- Result: Both return 5.
4 
5-- Multilingual Hindi / Emojis:
6SELECT LENGTH('भारत'), CHAR_LENGTH('भारत');
7-- Result: LENGTH = 12 bytes! CHAR_LENGTH = 4 characters!

LENGTH(str): Returns length in bytes.
CHAR_LENGTH(str): Returns length in characters. Always use CHAR_LENGTH when measuring text length for user validation!

4. Substring Extraction: `SUBSTRING()` / `SUBSTR()`

Extracts a slice of text starting at a specific position. Note: SQL indexes strings starting at 1, NOT 0!

1-- Syntax: SUBSTRING(string, start_position, [length])
2 
3-- Extract first 3 characters (e.g. Area Code):
4SELECT SUBSTRING(phone_number, 1, 3) FROM contacts;
5 
6-- Extract from position 5 to the end:
7SELECT SUBSTRING('MSK-TECH-ACADEMY', 5); -- 'TECH-ACADEMY'
8 
9-- Extract domain from email using SUBSTRING and LOCATE/INSTR:
10SELECT 
11    email,
12    SUBSTRING(email, INSTR(email, '@') + 1) AS email_domain
13FROM users;

5. Cleaning Text: `TRIM()`, `LTRIM()`, and `RTRIM()`

Removes unwanted leading and trailing whitespace characters:

1-- Sanitize user input:
2SELECT TRIM('   clean text   ') AS cleaned; -- 'clean text'
3 
4-- Trim specific characters:
5SELECT TRIM(BOTH '#' FROM '###Special Offer###'); -- 'Special Offer'

6. String Replacement: `REPLACE()`

Replaces all occurrences of a search string with a replacement string:

1-- Update old domain references in stored URLs:
2SELECT REPLACE('https://old-domain.com/page1', 'old-domain.com', 'mskinstitute.in') AS new_url;

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

In SQL string indexing (e.g., in the SUBSTRING() function), what is the index position of the first character?

Question 2

What does CONCAT('Hello', NULL, 'World') return in MySQL?

Question 3

Which string concatenation function automatically uses a specified delimiter and skips any NULL arguments?

Question 4

Which function measures the human-perceived count of characters rather than the physical byte count of a Unicode string?

Question 5

What is the output of SELECT SUBSTRING('DATABASE', 5, 4);?

Next Lesson

Numeric & Math Functions

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Pagination with LIMIT and OFFSET	Numeric & Math Functions

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

SQL String Functions: Manipulation, Extraction, and Formatting

1. String Concatenation: CONCAT() and CONCAT_WS()

CONCAT(str1, str2, ...)

CONCAT_WS(separator, str1, str2, ...) (Concatenate With Separator)

2. Case Conversion: UPPER() and LOWER()

3. String Length: LENGTH() vs CHAR_LENGTH()

4. Substring Extraction: SUBSTRING() / SUBSTR()

5. Cleaning Text: TRIM(), LTRIM(), and RTRIM()

6. String Replacement: REPLACE()

Multiple Choice Questions

Numeric & Math Functions

Related Lessons

Practice Quiz

Loading Page Content...

SQL String Functions: Manipulation, Extraction, and Formatting

1. String Concatenation: CONCAT() and CONCAT_WS()

CONCAT(str1, str2, ...)

CONCAT_WS(separator, str1, str2, ...) (Concatenate With Separator)

2. Case Conversion: UPPER() and LOWER()

3. String Length: LENGTH() vs CHAR_LENGTH()

4. Substring Extraction: SUBSTRING() / SUBSTR()

5. Cleaning Text: TRIM(), LTRIM(), and RTRIM()

6. String Replacement: REPLACE()

Multiple Choice Questions

Numeric & Math Functions

Related Lessons

Practice Quiz

1. String Concatenation: `CONCAT()` and `CONCAT_WS()`

`CONCAT(str1, str2, ...)`

`CONCAT_WS(separator, str1, str2, ...)` (Concatenate With Separator)

2. Case Conversion: `UPPER()` and `LOWER()`

3. String Length: `LENGTH()` vs `CHAR_LENGTH()`

4. Substring Extraction: `SUBSTRING()` / `SUBSTR()`

5. Cleaning Text: `TRIM()`, `LTRIM()`, and `RTRIM()`

6. String Replacement: `REPLACE()`

1. String Concatenation: `CONCAT()` and `CONCAT_WS()`

`CONCAT(str1, str2, ...)`

`CONCAT_WS(separator, str1, str2, ...)` (Concatenate With Separator)

2. Case Conversion: `UPPER()` and `LOWER()`

3. String Length: `LENGTH()` vs `CHAR_LENGTH()`

4. Substring Extraction: `SUBSTRING()` / `SUBSTR()`

5. Cleaning Text: `TRIM()`, `LTRIM()`, and `RTRIM()`

6. String Replacement: `REPLACE()`