Skip to main content

MySQL String Functions

Introduction​

String functions in MySQL are essential tools that allow you to manipulate and process text data stored in your database. Whether you need to format text for display, search for specific patterns, or transform data for analysis, MySQL's string functions provide powerful capabilities to handle these tasks efficiently.

In this guide, we'll explore the most commonly used MySQL string functions with practical examples that demonstrate their real-world applications. By mastering these functions, you'll be able to process text data more effectively in your database operations.

Basic String Functions​

CONCAT() and CONCAT_WS()​

The CONCAT() function combines two or more strings into a single string, while CONCAT_WS() (Concatenate With Separator) joins strings with a specified separator.

Example: CONCAT()​

sql
SELECT CONCAT('Hello', ' ', 'World') AS greeting;

Output:

+-------------+
| greeting    |
+-------------+
| Hello World |
+-------------+

Example: CONCAT_WS()​

sql
SELECT CONCAT_WS('-', 'MySQL', 'String', 'Functions') AS concatenated_string;

Output:

+-------------------------+
| concatenated_string     |
+-------------------------+
| MySQL-String-Functions  |
+-------------------------+

LENGTH() and CHAR_LENGTH()​

The LENGTH() function returns the length of a string in bytes, while CHAR_LENGTH() returns the number of characters.

sql
SELECT 
    LENGTH('Hello') AS length_bytes,
    CHAR_LENGTH('Hello') AS length_chars;

Output:

+-------------+--------------+
| length_bytes| length_chars |
+-------------+--------------+
| 5           | 5            |
+-------------+--------------+

For multi-byte character sets, LENGTH() and CHAR_LENGTH() can return different values:

sql
SELECT 
    LENGTH('こんにちは') AS length_bytes,
    CHAR_LENGTH('こんにちは') AS length_chars;

Output:

+-------------+--------------+
| length_bytes| length_chars |
+-------------+--------------+
| 15          | 5            |
+-------------+--------------+

Case Conversion Functions​

MySQL provides several functions to change the case of string values.

UPPER() and LOWER()​

sql
SELECT 
    UPPER('hello world') AS uppercase,
    LOWER('HELLO WORLD') AS lowercase;

Output:

+-------------+-------------+
| uppercase   | lowercase   |
+-------------+-------------+
| HELLO WORLD | hello world |
+-------------+-------------+

String Extraction Functions​

LEFT() and RIGHT()​

These functions extract a specified number of characters from the beginning or end of a string.

sql
SELECT 
    LEFT('MySQL Functions', 5) AS left_extract,
    RIGHT('MySQL Functions', 9) AS right_extract;

Output:

+-------------+--------------+
| left_extract| right_extract|
+-------------+--------------+
| MySQL       | Functions    |
+-------------+--------------+

SUBSTRING()​

The SUBSTRING() function extracts a portion of a string.

sql
-- Syntax: SUBSTRING(string, start_position, length)
SELECT SUBSTRING('MySQL String Functions', 7, 6) AS substring_result;

Output:

+------------------+
| substring_result |
+------------------+
| String           |
+------------------+

You can also use SUBSTR() as an alias for SUBSTRING().

SUBSTRING_INDEX()​

This function returns a substring before or after a specified number of occurrences of a delimiter.

sql
-- Syntax: SUBSTRING_INDEX(string, delimiter, count)
SELECT SUBSTRING_INDEX('www.example.com', '.', 1) AS domain_prefix;

Output:

+---------------+
| domain_prefix |
+---------------+
| www           |
+---------------+

To get the domain suffix:

sql
SELECT SUBSTRING_INDEX('www.example.com', '.', -1) AS domain_suffix;

Output:

+---------------+
| domain_suffix |
+---------------+
| com           |
+---------------+

String Modification Functions​

TRIM(), LTRIM(), and RTRIM()​

These functions remove spaces or specified characters from strings.

sql
SELECT 
    TRIM('  MySQL  ') AS trimmed,
    LTRIM('  MySQL  ') AS left_trimmed,
    RTRIM('  MySQL  ') AS right_trimmed;

Output:

+---------+-------------+-------------+
| trimmed | left_trimmed| right_trimmed|
+---------+-------------+-------------+
| MySQL   | MySQL       | MySQL       |
+---------+-------------+-------------+

You can also specify characters to remove:

sql
SELECT TRIM(BOTH '#' FROM '###MySQL###') AS trim_specific;

Output:

+--------------+
| trim_specific|
+--------------+
| MySQL        |
+--------------+

REPLACE()​

This function replaces occurrences of a substring within a string.

sql
-- Syntax: REPLACE(string, substring_to_find, replacement_string)
SELECT REPLACE('MySQL is fast, MySQL is reliable', 'MySQL', 'MariaDB') AS replaced;

Output:

+----------------------------------------+
| replaced                               |
+----------------------------------------+
| MariaDB is fast, MariaDB is reliable   |
+----------------------------------------+

REPEAT()​

This function repeats a string a specified number of times.

sql
SELECT REPEAT('MySQL ', 3) AS repeated;

Output:

+---------------------+
| repeated            |
+---------------------+
| MySQL MySQL MySQL   |
+---------------------+

INSERT()​

The INSERT() function inserts a substring at a specified position in a string.

sql
-- Syntax: INSERT(original_string, position, length, new_substring)
SELECT INSERT('MySQL Database', 7, 0, 'Relational ') AS inserted;

Output:

+-----------------------+
| inserted              |
+-----------------------+
| MySQL Relational Database |
+-----------------------+

String Comparison and Searching​

STRCMP()​

This function compares two strings.

sql
SELECT 
    STRCMP('MySQL', 'MySQL') AS equal,
    STRCMP('MySQL', 'mysql') AS case_sensitive,
    STRCMP('MySQL', 'MariaDB') AS different;

Output:

+-------+---------------+----------+
| equal | case_sensitive| different|
+-------+---------------+----------+
| 0     | 0             | 1        |
+-------+---------------+----------+

LOCATE() and POSITION()​

These functions find the position of a substring within a string.

sql
-- Syntax: LOCATE(substring, string, [start_position])
SELECT 
    LOCATE('String', 'MySQL String Functions') AS locate_result,
    POSITION('String' IN 'MySQL String Functions') AS position_result;

Output:

+---------------+------------------+
| locate_result | position_result  |
+---------------+------------------+
| 7             | 7                |
+---------------+------------------+

Real-world Applications​

Example 1: Formatting Names in a Database​

Let's say you have a table with users' first and last names, and you want to display the full name with proper capitalization:

sql
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100)
);

INSERT INTO users (first_name, last_name, email) VALUES 
('john', 'doe', '[email protected]'),
('JANE', 'SMITH', '[email protected]'),
('robert', 'JOHNSON', '[email protected]');

SELECT 
    id,
    CONCAT(
        UPPER(LEFT(first_name, 1)), 
        LOWER(SUBSTRING(first_name, 2))
    ) AS formatted_first_name,
    CONCAT(
        UPPER(LEFT(last_name, 1)), 
        LOWER(SUBSTRING(last_name, 2))
    ) AS formatted_last_name,
    email
FROM users;

Output:

+----+--------------------+--------------------+------------------------+
| id | formatted_first_name | formatted_last_name | email                  |
+----+--------------------+--------------------+------------------------+
| 1  | John               | Doe                | [email protected]   |
| 2  | Jane               | Smith              | [email protected] |
| 3  | Robert             | Johnson            | [email protected]   |
+----+--------------------+--------------------+------------------------+

Example 2: Extracting Information from URLs​

If you have a table storing website URLs, you might want to extract domain names:

sql
CREATE TABLE websites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    url VARCHAR(255),
    name VARCHAR(100)
);

INSERT INTO websites (url, name) VALUES 
('https://www.example.com/page1', 'Example Site'),
('http://blog.example.org/post/123', 'Example Blog'),
('https://subdomain.another-example.net/resource', 'Another Site');

SELECT 
    name,
    url,
    SUBSTRING_INDEX(SUBSTRING_INDEX(REPLACE(REPLACE(url, 'https://', ''), 'http://', ''), '/', 1), '.', -2) AS domain_name
FROM websites;

Output:

+--------------+------------------------------------------+------------------+
| name         | url                                      | domain_name      |
+--------------+------------------------------------------+------------------+
| Example Site | https://www.example.com/page1           | example.com      |
| Example Blog | http://blog.example.org/post/123        | example.org      |
| Another Site | https://subdomain.another-example.net/resource | another-example.net |
+--------------+------------------------------------------+------------------+

Example 3: Masking Sensitive Information​

To mask parts of sensitive data like credit card numbers or email addresses:

sql
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    credit_card VARCHAR(19)
);

INSERT INTO customers (name, email, credit_card) VALUES 
('Alice Brown', '[email protected]', '4111-2222-3333-4444'),
('Bob Wilson', '[email protected]', '5555-6666-7777-8888'),
('Carol Davis', '[email protected]', '9999-0000-1111-2222');

SELECT 
    name,
    CONCAT(SUBSTRING(email, 1, 2), '***', SUBSTRING_INDEX(email, '@', -1)) AS masked_email,
    CONCAT('XXXX-XXXX-XXXX-', RIGHT(credit_card, 4)) AS masked_card
FROM customers;

Output:

+-------------+-------------------------+---------------------+
| name        | masked_email           | masked_card         |
+-------------+-------------------------+---------------------+
| Alice Brown | al***@example.com      | XXXX-XXXX-XXXX-4444 |
| Bob Wilson  | bo***@example.com      | XXXX-XXXX-XXXX-8888 |
| Carol Davis | ca***@example.com      | XXXX-XXXX-XXXX-2222 |
+-------------+-------------------------+---------------------+

Advanced String Functions​

REGEXP and REGEXP_LIKE()​

MySQL allows pattern matching using regular expressions.

sql
SELECT 'MySQL' REGEXP '^My' AS starts_with_my;

Output:

+----------------+
| starts_with_my |
+----------------+
| 1              |
+----------------+

For more complex pattern matching:

sql
SELECT 
    '[email protected]' REGEXP '[a-z]+\\.[a-z]+@[a-z]+\\.[a-z]+' AS valid_email_pattern,
    REGEXP_LIKE('123-45-6789', '^[0-9]{3}-[0-9]{2}-[0-9]{4}$') AS valid_ssn_pattern;

Output:

+--------------------+------------------+
| valid_email_pattern| valid_ssn_pattern|
+--------------------+------------------+
| 1                  | 1                |
+--------------------+------------------+

FORMAT()​

This function formats a number with a specified number of decimal places and adds thousand separators.

sql
SELECT FORMAT(1234567.89, 2) AS formatted_number;

Output:

+------------------+
| formatted_number |
+------------------+
| 1,234,567.89     |
+------------------+

Performance Considerations​

When working with string functions, keep these performance tips in mind:

  1. Indexing: String operations on indexed columns can sometimes be slower because MySQL may not be able to use the index.
  2. Function-based Indexes: In some MySQL versions, you can create indexes on expressions involving functions.
  3. Large Text Processing: For operations on very large text columns, consider using dedicated text processing tools outside of MySQL.

Summary​

MySQL string functions provide powerful capabilities for text manipulation within your database queries. We've covered the most common and useful string functions including:

  • Basic functions like CONCAT(), LENGTH(), and case conversion
  • Extraction functions such as SUBSTRING() and LEFT()/RIGHT()
  • Modification functions including TRIM(), REPLACE(), and INSERT()
  • Comparison and search functions like STRCMP() and LOCATE()
  • Advanced pattern matching with regular expressions

By mastering these functions, you can efficiently process and manipulate text data directly in your MySQL queries, reducing the need for post-processing in application code.

Practice Exercises​

  1. Create a query that extracts the username from email addresses (the part before the @ symbol).
  2. Write a SQL statement to properly capitalize names (first letter uppercase, rest lowercase).
  3. Create a function to mask all but the last four digits of a phone number.
  4. Write a query to find all records in a table where a text column contains a specific word regardless of case.
  5. Create a query to extract hashtags from a text field (words starting with #).

Additional Resources​

Remember that string manipulation in databases should be used judiciously. For complex text processing, consider whether the operation might be better performed in application code rather than SQL queries, especially when dealing with large datasets.


If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)