Common MySQL String Functions

MySQL provides a variety of built‑in string functions for text manipulation, including case conversion, concatenation, substring extraction, length measurement, padding, trimming, replacement, repetition, reversal, and numeric formatting.

lower() – converts a string to lowercase.

mysql> select lower("KFDSKGS");
+------------------+
| lower("KFDSKGS") |
+------------------+
| kfdskgs          |
+------------------+

upper() – converts a string to uppercase.

mysql> select upper("fdssssssss");
+-------------------+
| upper("fdssssssss") |
+-------------------+
| FDSSSSSSSS      |
+-------------------+

concat() – concatenates multiple strings.

mysql> select concat("My","S","QL");
+--------------------+
| concat("My","S","QL") |
+--------------------+
| MySQL              |
+--------------------+

substring() / substr() – extracts a substring.

mysql> select substring("Hello world",5);
+-----------------------+
| substring("Hello world",5) |
+-----------------------+
| o world               |
+-----------------------+
1 row in set (0.07 sec)

mysql> select substring("Hhllo world",5,3);
+-------------------------+
| substring("Hhllo world",5,3) |
+-------------------------+
| o w                     |
+-------------------------+
1 row in set (0.07 sec)

mysql> select substring("Hhllo world",-5);
+------------------------+
| substring("Hhllo world",-5) |
+------------------------+
| world                  |
+------------------------+
1 row in set (0.08 sec)

length() – returns the byte length of a string (UTF‑8 characters may occupy multiple bytes).

mysql> select length("text");
+-------------+
| length("text") |
+-------------+
|           4 |
+-------------+
mysql> select length("你好");
+--------------+
| length("你好") |
+--------------+
|            6 |
+--------------+

char_length() – returns the character count.

mysql> select char_length("text");
+------------------+
| char_length("text") |
+------------------+
|                4 |
+------------------+
1 row in set (0.07 sec)

instr() – finds the position of a substring.

mysql> select instr("footer","ter");
+-----------------+
| instr("footer","ter") |
+-----------------+
|           4 |
+-----------------+

lpad() – left‑pads a string to a specified length.

mysql> select lpad("hello",10,"0");
+-----------------+
| lpad("hello",10,"0") |
+-----------------+
| 00000hello       |
+-----------------+
1 row in set (0.13 sec)

rpad() – right‑pads a string to a specified length.

mysql> select rpad("hello",10,"0");
+------------------+
| rpad("hello",10,"0") |
+------------------+
| hello00000        |
+------------------+
1 row in set (0.13 sec)

trim() – removes leading and trailing spaces.

mysql> select trim('   bar   ');
+-------------+
| trim('   bar   ') |
+-------------+
| bar         |
+-------------+
1 row in set (0.13 sec)

trim(leading ...) – removes specified leading characters.

mysql> select trim(leading 'X' from 'XXXXXtrimleadingXXXX');
+---------------------------------------+
| trim(leading 'X' from 'XXXXXtrimleadingXXXX') |
+---------------------------------------+
| trimleadingXXXX                       |
+---------------------------------------+
1 row in set (0.13 sec)

trim(trailing ...) – removes specified trailing characters.

mysql> select trim(trailing 'zyz' from 'barxxyz');
+----------------------------+
| trim(trailing 'zyz' from 'barxxyz') |
+----------------------------+
| barxxyz                    |
+----------------------------+
1 row in set (0.12 sec)

trim(both ...) – removes specified characters from both ends.

mysql> trim(both 'X' from 'XXXXXXXtrimbothXXXXXXX')
+----------------------------------------+
| trim(both 'X' from 'XXXXXXXtrimbothXXXXXXX') |
+----------------------------------------+
| trimboth                               |
+----------------------------------------+
1 row in set (0.13 sec)

replace() – replaces occurrences of a substring.

mysql> select replace('www.mysql.com','w','W');
+------------------------------+
| replace('www.mysql.com','w','W') |
+------------------------------+
| WWW.mysql.com               |
+------------------------------+

repeat() – repeats a string a given number of times.

mysql> select repeat('MySQL',3);
+--------------------+
| repeat('MySQL',3)    |
+--------------------+
| MySQLMySQLMySQL |
+--------------------+
1 row in set (0.07 sec)

reverse() – reverses a string.

mysql> select reverse("abcdef");
+-----------------+
| reverse("abcdef") |
+-----------------+
| fedcba          |
+-----------------+
1 row in set (0.06 sec)

format() – formats a number with commas and a specified number of decimal places.

mysql> select format(129021,4);
+----------------+
| format(129021,4) |
+----------------+
| 129,021.0000   |
+----------------+

These functions are essential tools for handling textual data in MySQL queries.