MySQL string functions
String functions are used to perform an operation on an input string and return an output string. Some MYSQL string functions and their uses:
- ASCII: Returns the ASCII value for the specific character Example: SELECT ASCII('A'); returns 65
- CHAR_LENGTH: Returns the length of a string (in characters) Example: SELECT CHAR_LENGTH("SQL Tutorial") AS LengthOfString; returns 12
- CHARACTER_LENGTH: Returns the length of a string (in characters) Example: SELECT CHARACTER_LENGTH("SQL Tutorial") AS LengthOfString; returns 12
- CONCAT: Adds two or more expressions together
Example: SELECT CONCAT("SQL ", "Tutorial ", "is ", "fun!") AS ConcatenatedString; returns SQL Tutorial is fun! - CONCAT_WS: Adds two or more expressions together with a separator Example: SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") AS ConcatenatedString; returns SQL-Tutorial-is-fun!
- FIELD: Returns the index position of a value in a list of values Example: SELECT FIELD("q", "s", "q", "l"); returns 2
- FIND_IN_SET: Returns the position of a string within a list of strings Example: SELECT FIND_IN_SET("q", "s,q,l"); returns 2
- FORMAT: Formats a number to a format like "#,###,###.##", rounded to a specified number of decimal places Example: SELECT FORMAT(250500.5634, 2); returns 250,500.56
- INSERT: Insert a string within a string at the specified position and for a certain number of characters Example: SELECT INSERT("W3Schools.com", 1, 9, "Example"); returns Example.com
- INSTR: Returns the position of the first occurrence of a string in another string Example: SELECT INSTR("W3Schools.com", "3") AS MatchPosition; returns 2
- LCASE: Converts a string to lower-case Example: SELECT LCASE("SQL Tutorial is FUN!") AS LowercaseText; returns sql tutorial is fun!
- LEFT: Extracts a number of characters from a string (starting from left) Example: SELECT LEFT("SQL Tutorial", 3) AS ExtractString; returns SQL
- LENGTH: Returns the length of a string (in bytes) Example: SELECT LENGTH("SQL Tutorial") AS LengthOfString; returns 12
- LOCATE: Returns the position of the first occurrence of a substring in a string Example: SELECT LOCATE("3", "W3Schools.com") AS MatchPosition; returns 2
- LOWER: Converts a string to lower-case Example: SELECT LOWER("SQL Tutorial is FUN!"); returns sql tutorial is fun!
- LPAD: Left-pads a string with another string, to a certain length Example: SELECT LPAD("SQL Tutorial", 20, "ABC"); returns ABCABCABSQL Tutorial
- LTRIM: Removes leading spaces from a string Example: SELECT LTRIM(" SQL Tutorial") AS LeftTrimmedString; returns SQL Tutorial
- MID: Extracts a substring from a string (starting at any position) Example: SELECT MID("SQL Tutorial", 5, 3) AS ExtractString; returns Tut
- POSITION: Returns the position of the first occurrence of a substring in a string Example: SELECT POSITION("3" IN "W3Schools.com") AS MatchPosition; returns 2
- REPEAT: Repeats a string as many times as specified Example: SELECT REPEAT("SQL Tutorial", 3); returns SQL TutorialSQL TutorialSQL Tutorial
- REPLACE: Replaces all occurrences of a substring within a string, with a new substring Example: SELECT REPLACE("SQL Tutorial", "SQL", "HTML"); returns HTML Tutorial
- REVERSE: Reverses a string and returns the result Example: SELECT REVERSE("SQL Tutorial"); returns lairotuT LQS
- RIGHT: Extracts a number of characters from a string (starting from right) Example: SELECT RIGHT("SQL Tutorial is cool", 4) AS ExtractString; returns cool
- RPAD: Right-pads a string with another string, to a certain length Example: SELECT RPAD("SQL Tutorial", 20, "ABC"); returns SQL TutorialABCABCAB
- RTRIM: Removes trailing spaces from a string Example: SELECT RTRIM("SQL Tutorial ") AS RightTrimmedString; returns SQL Tutorial
- SPACE: Returns a string of the specified number of space characters Example: SELECT SPACE(10); returns “ “
- STRCMP: Compares two strings Example: SELECT STRCMP("SQL Tutorial","SQL Tutorial"); returns 0
- SUBSTR: Extracts a substring from a string (starting at any position) Example: SELECT SUBSTR("SQL Tutorial", 5, 3) AS ExtractString; returns Tut
- SUBSTRING: Extracts a substring from a string (starting at any position) Example: SELECT SUBSTRING("SQL Tutorial", 5, 3) AS ExtractString; returns Tut
- SUBSTRING_INDEX: Returns a substring of a string before a specified number of delimiters occurs Example: SELECT SUBSTRING_INDEX("w3schools.com", ".", 2); returns w3schools SELECT SUBSTRING_INDEX("w3schools.com", ".", 1); returns www
- TRIM: Removes leading and trailing spaces from a string Example: SELECT TRIM(" SQL Tutorial ") AS TrimmedString; returns SQL Tutorial
- UCASE: Converts a string to upper-case Example: SELECT UCASE("SQL Tutorial is FUN!"); returns SQL TUTORIAL IS FUN!
- UPPER: Converts a string to upper-case Example: SELECT UPPER("SQL Tutorial is FUN!"); returns SQL TUTORIAL IS FUN!