String Functions in MySQL

In MySQL, string functions are used to manipulate and analyze string data. String functions in MySQL are the surgical toolkit that transforms raw, messy text into clean, structured, and meaningful data, enabling everything from search optimization to automated reporting with surgical precision.

These built-in functions manipulate character data directly within queries, eliminating the need for external processing in application code. At their core, they handle concatenation, trimming, case conversion, substring extraction, pattern matching, and length calculation—each designed to run at database speed on millions of rows. CONCAT() merges fields like first_name and last_name into full_name, while CONCAT_WS() adds separators intelligently, perfect for CSV exports. TRIM(), LTRIM(), and RTRIM() strip unwanted spaces or characters, cleaning imported data in one pass. UPPER() and LOWER() standardize case for consistent searches, while SUBSTRING() and MID() extract phone digits, order codes, or dates embedded in strings. LENGTH() and CHAR_LENGTH() measure byte versus character count, critical for Unicode handling in multilingual apps. REPLACE() swaps outdated values globally, and INSTR() locates substrings for conditional logic.

Advanced pattern matching with LIKE, REGEXP, and REGEXP_SUBSTR() powers fuzzy search, email validation, and data masking. In lecture terms, string functions are the database’s native text processor: they enforce data quality at the source, accelerate ETL pipelines, and enable real-time personalization. Used wisely with indexes on computed columns, they scale from user-facing search bars to backend data hygiene. Mastery means turning “john.doe@email.com” into “John Doe (jdoe)” in a single SELECT, or flagging all records with malformed Nigerian phone numbers before they corrupt your CRM.

Practical use of string functions:

• Generate full names with CONCAT(first_name, ‘ ‘, last_name) AS full_name
• Clean imported CSVs using TRIM(BOTH ‘ ‘ FROM dirty_field)
• Extract domain from email with SUBSTRING_INDEX(email, ‘@’, -1)
• Validate US phone numbers with REGEXP ‘^\+1[789][01]\d{8}$’
• Create URL slugs using LOWER(REPLACE(title, ‘ ‘, ‘-‘))
• Mask sensitive data: CONCAT(LEFT(ssn, 3), ‘-XX-XXXX’)
• Find records containing keywords with column LIKE ‘%search_term%’
• Pad invoice numbers: LPAD(order_id, 8, ‘0’)
• Count words in product descriptions with (LENGTH(description) – LENGTH(REPLACE(description, ‘ ‘, ”)) + 1)
• Split full address into street, city using SUBSTRING_INDEX()
• Format phone for display: INSERT(INSERT(phone, 7, 0, ‘-‘), 4, 0, ‘-‘)
• Remove HTML tags with REGEXP_REPLACE(content, ‘<[^>]+>’, ”)
• Generate initials: CONCAT(LEFT(first_name,1), LEFT(last_name,1))
• Detect duplicate emails ignoring case with LOWER(email) in GROUP BY
• Extract year from free-text logs using REGEXP_SUBSTR(log_entry, ‘[0-9]{4}’)

Commonly used String functions

These functions provide flexibility and ease in handling various string operations in MySQL queries

1. `CONCAT()`

-Purpose: Combines two or more strings into one.

-Eg

  SELECT CONCAT(‘Hello’, ‘ ‘, ‘World’) AS Result;

  Result: ‘Hello World’

2. `SUBSTRING()` or `SUBSTR()`

-Purpose: Extracts a substring from a string.

Eg

  SELECT SUBSTRING(‘Hello World’, 1, 5) AS Result;

  -Result: ‘Hello’

3. `LENGTH()`

– Purpose: Returns the length of a string in bytes.

Eg

  SELECT LENGTH(‘Hello’) AS Result;

  – Result: 5

4. `LOWER()` and `UPPER()`

Purpose: Converts a string to lowercase or uppercase.

– Example:

  SELECT LOWER(‘HELLO’) AS Result;

  — Result: ‘hello’

  SELECT UPPER(‘hello’) AS Result;

  — Result: ‘HELLO’

5. `TRIM()`

Purpose: Removes leading and trailing spaces from a string.

  SELECT TRIM(‘  Hello  ‘) AS Result;

  — Result: ‘Hello’

6. `REPLACE()`

– **Purpose**: Replaces occurrences of a specified string with another string.

Eg

  SELECT REPLACE(‘Hello World’, ‘World’, ‘MySQL’) AS Result;

  — Result: ‘Hello MySQL’

7. `INSTR()`

-Purpose: Returns the position of the first occurrence of a substring.

Eg

  SELECT INSTR(‘Hello World’, ‘World’) AS Result;

  — Result: 7

8. `LPAD()` and `RPAD()`

– **Purpose**: Pads a string with another string to a specified length.

Eg:

  SELECT LPAD(‘Hello’, 10, ‘*’) AS Result;

  — Result: ‘*****Hello’

  SELECT RPAD(‘Hello’, 10, ‘*’) AS Result;

  — Result: ‘Hello*****’

9. `REVERSE()`

-Purpose: Reverses the characters in a string.

Eg

  SELECT REVERSE(‘Hello’) AS Result;

  — Result: ‘olleH’

10. `FORMAT()`

Purpose:-  Formats a number as a string with grouped thousands and optional decimal places.

-Example:

  SELECT FORMAT(1234567.89, 2) AS Result;

  — Result: ‘1,234,567.89’

MODULE XXD Case statement 

A ‘CASE’ statement in MySQL allows you to implement conditional logic within your SQL queries. It evaluates a series of conditions and returns a value when the first condition is met. If no conditions are met, it returns an optional default value. The ‘CASE’ statement can be used in ‘SELECT’, ‘WHERE’, ‘ORDER BY’, and other SQL clauses.

Syntax

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    ELSE default_result

END

Example 

SELECT

    employee_id,

    salary,

    CASE

        WHEN salary > 50000 THEN ‘High’

        WHEN salary BETWEEN 30000 AND 50000 THEN ‘Medium’

        ELSE ‘Low’

    END AS salary_range

FROM

    employees;

In this example, the query categorizes employee salaries into ‘High’, ‘Medium’, or ‘Low’ based on the salary amount. The **CASE** statement is evaluated for each row, and the first condition that is true determines the output for the **salary_range** column.