Updated May 9, 2023
Introduction to MySQL String Functions
MySQL has several pre-defined sets of string functions, which can be applied to string characters to perform several operations in SQL programming scenarios. A few of the commonly used operations are RIGHT, used to fetch the number of characters in the rightmost part of the string, CONCAT used for string concatenation, SPACE used to get the return value for a number of space characters in a string, INSERT used to insert a new string within another string specific to the position with the number of characters to be inserted, etc.
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
The 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 the 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 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 the 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 resulting 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 the 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 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, the sub-string before the occurrence is returned. If the count is negative, the 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
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 framework’s power to its total capacity.
Recommended Articles
We hope that this EDUCBA information on “MySQL String functions” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
