MySQL has many built-in functions. MySQL String Functions reference contains the string, numeric, date, and advanced functions in MySQL.
1. ASCII
Returns the number code that represents the first character.
|
1 2 3 4 5 6 |
SELECT FirstName, ASCII(FirstName) AS NumCodeOfFirstChar FROM Users; |
Result:
| FirstName | NumCodeOfFirstChar |
|---|---|
| 123456 | 49 |
| admin | 97 |
| aakash | 97 |
| jagdish | 106 |
| Ashish | 65 |
| Vishal | 86 |
2. CHAR_LENGTH
Returns the length of the string
|
1 2 3 4 5 |
SELECT CHAR_LENGTH("We Answer Website") AS StringLength |
Result:
| StringLength |
|---|
| 17 |
3. CHARACTER_LENGTH
Returns the length of the string
|
1 2 3 4 5 |
SELECT CHARACTER_LENGTH("We Answer Website") AS StringLength |
Result:
| StringLength |
|---|
| 17 |
4. CONCAT
Join two or more string together
|
1 2 3 4 5 |
SELECT CONCAT("We", "Answer", ".", "xyz") AS Str |
Result:
| Str |
|---|
| WeAnswer.xyz |
5. CONCAT_WS
Join two or more string together and adds a separator between them
|
1 2 3 4 5 |
SELECT CONCAT_WS("*", "We", "Answer", "xyz") AS Str |
Result:
| Str |
|---|
| We*Answer*xyz |
6. FIND_IN_SET
return position in a list.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT FIELD("a", "a", "b", "c", "d", "e") ; Result:1 SELECT FIELD("c", "a", "b", "c", "d", "e") ; Result:3 SELECT FIELD("z", "a", "b", "c", "d", "e") ; Result:0 |
7. FIND_IN_SET
return position a string in a string list.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT FIND_IN_SET("a", "a,b,c,d,e"); Result:1 SELECT FIND_IN_SET("c", "a,b,c,d,e"); Result:3 SELECT FIND_IN_SET("z", "a,b,c,d,e"); Result:0 |
8. FORMAT
Format the number as a format of “*,***.**”
|
1 2 3 4 5 6 7 8 9 |
SELECT FORMAT(250500.5634, 2); Result: 250,500.56 SELECT FORMAT(250500.5634, 3); Result: 250,500.563 |
9. INSERT
Replace the string with characters:
|
1 2 3 4 5 6 7 8 |
SELECT INSERT("weanswer", 1, 2, "xyz"); Result: xyzanswer SELECT INSERT("weanswer", 2, 3, "xyz"); Result: wxyzswer |
10. INSTR
Return substring position
|
1 2 3 4 5 6 7 8 |
SELECT INSTR("weAnswer.xyz", "A") AS MatchPosition; Result: 3 SELECT INSTR("wAenswer.xyz", "A") AS MatchPosition; Result: 2 |
11. LCASE
Convert to lower case
|
1 2 3 4 5 6 7 8 |
SELECT LCASE("WeAnser.xyz") AS LowercaseText; Result: weanser.xyz SELECT LCASE("WOW") AS LowercaseText; Result: wow |
12. LEFT
Returns sub string from left
|
1 2 3 4 5 6 7 8 |
SELECT LEFT("MySQL String Functions", 3) AS ExtractString; Result: MyS SELECT LEFT("MySQL String Functions", 5) AS ExtractString; Result: MySQL |
13. LENGTH
Returns Length of string
|
1 2 3 4 5 6 7 8 |
SELECT LENGTH("MySQL String Functions") AS LengthOfString; Result: 22 SELECT LENGTH("MySQL String") AS LengthOfString; Result: 12 |
14. LOCATE
Returns the position of the first occurrence of a substring in a string
|
1 2 3 4 5 6 7 8 |
SELECT LOCATE("S", "MySQL String Functions") AS MatchPosition; Result: 3 SELECT LOCATE("S", "MySQL String") AS MatchPosition; Result: 3 |
15. LOWER
Convert to lower case as per LCASE
16. LPAD
Returns a string that is left-padded with a specified string to a certain length
|
1 2 3 4 5 6 7 8 |
SELECT LPAD("MySQL String Functions", 30, "ABC"); Result: ABCABCABMySQL String Functions SELECT LPAD("MySQL String Functions", 25, "ABC"); Result: ABCMySQL String Functions |
17. LTRIM
Returns a string that is left- trim
|
1 2 3 4 5 6 7 8 |
SELECT LTRIM(" MySQL String Functions") AS LeftTrimmedString; Result: MySQL String Functions SELECT LTRIM(" MySQL String Functions") AS LeftTrimmedString; Result: MySQL String Functions |
18. MID
Returns a string that is position start with and specific position
|
1 2 3 4 5 6 7 8 |
SELECT MID("MySQL String Functions", 10, 4) AS ExtractString; Result: L S SELECT MID("MySQL String Functions", 9, 4) AS ExtractString; Result: ring |
19. POSITION
Returns the position of the first occurrence of a substring in a string as per LOCATE
20. REPEAT
Returns a string that repeat number of times
|
1 2 3 4 5 6 7 8 |
SELECT REPEAT("MySQL", 3); Result: MySQLMySQLMySQL SELECT REPEAT("MySQL ", 3); Result: MySQL MySQL MySQL |
21. REPLACE
Returns a string that replace by substring
|
1 2 3 4 5 6 7 8 |
SELECT REPLACE("MySQL String Functions", "String", "Numeric"); Result: MySQL Numeric Functions SELECT REPLACE("MySQL String Functions", "String", "Advantage"); Result: MySQL Advantage Functions |
22. REVERSE
Returns a string that reverse by a string
|
1 2 3 4 5 6 7 8 |
SELECT REVERSE("MySQL String Functions"); Result: snoitcnuF gnirtS LQSyM SELECT REVERSE("MySQL String");; Result:gnirtS LQSyM |
23. RIGHT
Returns sub string from right
|
1 2 3 4 5 6 7 8 |
SELECT RIGHT("MySQL String Functions", 4) AS ExtractString; Result: ions SELECT RIGHT("MySQL String Functions",9) AS ExtractString; Result:Functions |
24. RIGHT
Returns a string that is left-padded with a specified string to a certain length
|
1 2 3 4 5 6 7 8 |
SELECT RPAD("MySQL String Functions", 30, "ABC"); Result:MySQL String FunctionsABCABCAB SELECT RPAD("MySQL String Functions", 25, "ABC"); Result: MySQL String FunctionsABC |
25. RTRIM
Returns a string that is right- trim
|
1 2 3 4 5 6 7 8 |
SELECT RTRIM("MySQL String Functions. ", 30, "ABC"); Result:MySQL String Functions SELECT RPAD("MySQL String Functions. ", 25, "ABC"); Result: MySQL String Functions |
26. SPACE
returns a string with a specified number of spaces.
|
1 2 3 4 5 6 7 8 |
SELECT SPACE(10); Result: SELECT SPACE(6); Result: |
27. STRCMP
returns a string compression is true or not.
|
1 2 3 4 5 6 7 8 |
SELECT STRCMP("MySQL String Functions", "MySQL String Functions"); Result: 0 SELECT STRCMP("MySQL String Functions", "MySQL String Functions1323"); Result: -1 |
28. SUBSTR
Returns a string that is position start with and specific position as per MID
29. SUBSTRING
Returns a string that is position start with and specific position as per MID
30. SUBSTRING_INDEX
returns a string compression is true or not.
|
1 2 3 4 5 6 7 8 |
SELECT SUBSTRING_INDEX("MySQL String Functions", " ", 1); Result: MySQL SELECT SUBSTRING_INDEX("MySQL String Functions", "ing", 1); Result: MySQL Str |
31. SUBSTRING_INDEX
returns a string compression is true or not.
|
1 2 3 4 5 6 7 8 |
SELECT SUBSTRING_INDEX("MySQL String Functions", " ", 1); Result: MySQL SELECT SUBSTRING_INDEX("MySQL String Functions", "ing", 1); Result: MySQL Str |
32. TRIM
returns a trim string.
|
1 2 3 4 5 6 7 8 |
SELECT TRIM(" MySQL String Functions ") AS TrimmedString; Result: MySQL String Functions SELECT TRIM(" MySQL String Functions ") AS TrimmedString; Result: MySQL String Functions |
33. UCASE
Converts a string to upper-case
|
1 2 3 4 5 6 7 8 |
SELECT UCASE("MySQL String Functions") AS UppercaseText; Result: MYSQL STRING FUNCTIONS SELECT UCASE("MySQL") AS UppercaseText; Result: MYSQL |
34. UPPER
Converts a string to upper-case as per UCASE
More Stories
Incorrect datetime value: ‘0000-00-00 00:00:00’ for column
Compare comma-separated field with single string
Commonly used short form and their full form for web