MySQL String functions

Introduction to MySQL String functions

MySQL has various built-in functions to relieve the developers from simple tasks such as string formatting and let them focus on the solution to the bigger problem. This way, developers need not worry about writing scripts to carry out simple tasks. The built-in functions take care of that. Developers only need to focus on how and where to use these built-in functions to achieve the end result.

One such category of built-in functions is the MySQL string functions. String formatting such as concatenation, displaying in a certain format, inserting/deleting substrings, etc. can sometimes be a tedious task. MySQL string functions take care of that for you.

To illustrate various MySQL String functions, we first need to create a database. The following database will be referred to in all the examples:

MySQL String Functions

String function is easy to use. Here we will discuss how to use string functions in MySQL programming with the help of examples

1. ASCII(str)

Returns the ASCII value of the leftmost character of the string str.

Select FirstName, ASCII(FirstName) from Person

2. BIN(N)

Return binary equivalent of N, where N is an Int/BigInt.

Select Phone, BIN(Phone) from Person

3. BIT_LENGTH(str)

Returns the length of the string str in bits.

Select FirstName, BIT_LENGTH(FirstName) from Person

4. CHAR_LENGTH(str)

Returns the length of the string str in characters.

Select FirstName, CHAR_LENGTH(FirstName) from Person

5. CONCAT(str1, str2, …., strn)

Returns a string formed by joining str1 to strn. If any sub-string is NULL, the result is NULL.

Select FirstName, LastName, CONCAT(FirstName, LastName) as DisplayName from Person

6. CONCAT_WS(separator, str1, …, strn)

Returns a concatenated string separated by a separator.

Select FirstName, LastName, CONCAT_WS(' ', FirstName, LastName) as DisplayName from Person

Select FirstName, LastName, CONCAT_WS(', ', LastName, FirstName) as DisplayName from Person

7. FIELD(str, str1, str2, …, strn)

Returns the index position of string str amongst str1 to strn. Returns 0 if not found.

Select FirstName, LastName, FIELD('John', FirstName, LastName) as IsJohn from Person

8. FORMAT(x, D)

Formats the number X to ‘#,###,###.##’ for display. D is the number of decimal places. By default, the format is en-US.

Select FORMAT(Phone, 0) as Phone from Person

9. HEX(str) / UNHEX(hex_str)

Returns a hexadecimal equivalent of the string str. UNHEX returns the actual string.

Select FirstName, HEX(FirstName) from Person

10. INSERT(str, pos, len, newstr)

Replaces the sub-string starting at position pos and of length len in the string str with newstr.

Select FirstName, LastName, INSERT(LastName, 3, 20, FirstName) as LoginID from Person

11. INSTR(str, substr) / LOCATE(substr, str) / POSITION(substr IN str)

Returns the position of the first occurrence of substr in str.

Select FirstName, INSTR(FirstName, 'oo'), LOCATE('hn', FirstName), POSITION('al' IN FirstName) from Person

12. LCASE() / LOWER() and UCASE() / UPPER()

Converts a string to lower case and upper case.

Select FirstName, LOWER(FirstName) as Lower, UPPER(FirstName) as Upper from Person

13. LEFT(str, len) / RIGHT(str, len)

Returns the leftmost and rightmost len characters from the string str.

Select FirstName, LastName, CONCAT(LEFT(LastName, 3), RIGHT(FirstName, 2)) as LoginID from Person

14. LENGTH(str) / OCTET_LENGTH(str)

Returns the length of the string str in bytes. This is very much like CHAR_LENGTH function. The difference comes when there are multibyte characters in the string.

Select CHAR_LENGTH('€'), Length('€')

This happens because Euro (€) sign occupies 3 bytes in memory.

15. LPAD(str, len, padstr) / RPAD(str, len, padstr)

Inserts sub-string from position 0 of the string padstr at the beginning and end of the string str until the resultant string is of len characters.

Select FirstName, LastName, LPAD(CONCAT_WS(' ', FirstName, LastName), Char_Length(CONCAT_WS(' ', FirstName, LastName))+Char_Length('Mr. '), 'Mr. ') as DisplayName from Person

16. LTRIM(str) / RTRIM(str) / TRIM(str)

Returns the string str after trimming white spaces from left, right or both ends.

Select LTRIM('    abc    ') as L, RTRIM('    abc    ') as R, TRIM('    abc    ') as T

17. MID(str, pos, len) / SUBSTRING(str, pos, len)

Returns a substring from string str starting at position pos of length len.

Select FirstName, SUBSTRING(FirstName, 2, 4) as a sub from Person

18. QUOTE(str)

This query quotes and un-quotes the string str. Special characters are escaped.

Select Address, QUOTE(Address) from Person

19. REPLACE(str, from_str, to_str)

Replaces all occurrences of sub-string from_str with sub-string to_str in the string str. It is case-sensitive.

Select Address, REPLACE(Address, 'S', 'ss') from Person

20. REVERSE(str)

Reverses the string str.

Select FirstName, REVERSE(FirstName) from Person

21. SPACE(N)

This one is quite funny. It simply returns a string of N blank spaces. Now I wonder what made them create this function! Anyway, try it out yourself.

22. SUBSTR(str, pos)/SUBSTRING(str, pos)/SUBSTR(str, pos, len)/SUBSTRING(str, pos, len)

Returns a substring from the string str starting from position pos, till the end if no len is specified or for len characters otherwise. Remember, Index starts from 1 in MySQL.

Select FirstName, LastName, SUBSTR(FirstName, 2), SUBSTRING(LastName, 2, 2) from Person

23. SUBSTRING_INDEX(str, delim, count)

Returns a substring from the string str before or after the count occurrence of the delim character. If the count is positive, sub-string before the occurrence is returned. If the count is negative, sub-string after the occurrence is returned.

Select Address, SUBSTRING_INDEX(Address, '-', 1) as House, SUBSTRING_INDEX(Address, ',', 1) as Street, SUBSTRING_INDEX(Address, ',', -1) as City from Person

Conclusion – MySQL String functions

So, found some of these functions helpful? Did any of this help you solve a use-case you had? I am sure they do. Not just string functions, but MySQL built-in functions are a great way to achieve the results in minimal queries thereby re-using the existing code and leveraging the power of the framework to its full capacity.

Recommended Articles

This has been a guide to MySQL String functions. Here we discussed how to use string function in MySQL programming with the help of examples. You may also look at the following articles to learn more –

1. Different Types of MySQL Operators
2. How To Install MySQL?
3. Basic to Advance Commands of MySQL.
4. Cheat Sheet MySQL