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:

  1. ASCII: Returns the ASCII value for the specific character Example: SELECT ASCII('A'); returns 65
  2. CHAR_LENGTH: Returns the length of a string (in characters) Example: SELECT CHAR_LENGTH("SQL Tutorial") AS LengthOfString; returns 12
  3. CHARACTER_LENGTH: Returns the length of a string (in characters) Example: SELECT CHARACTER_LENGTH("SQL Tutorial") AS LengthOfString; returns 12
  4. CONCAT: Adds two or more expressions together
    Example: SELECT CONCAT("SQL ", "Tutorial ", "is ", "fun!") AS ConcatenatedString; returns SQL Tutorial is fun!
  5. 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!
  6. FIELD: Returns the index position of a value in a list of values Example: SELECT FIELD("q", "s", "q", "l"); returns 2
  7. 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
  8. 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
  9. 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
  10. INSTR: Returns the position of the first occurrence of a string in another string Example: SELECT INSTR("W3Schools.com", "3") AS MatchPosition; returns 2
  11. LCASE: Converts a string to lower-case Example: SELECT LCASE("SQL Tutorial is FUN!") AS LowercaseText; returns sql tutorial is fun!
  12. LEFT: Extracts a number of characters from a string (starting from left) Example: SELECT LEFT("SQL Tutorial", 3) AS ExtractString; returns SQL
  13. LENGTH: Returns the length of a string (in bytes) Example: SELECT LENGTH("SQL Tutorial") AS LengthOfString; returns 12
  14. LOCATE: Returns the position of the first occurrence of a substring in a string Example: SELECT LOCATE("3", "W3Schools.com") AS MatchPosition; returns 2
  15. LOWER: Converts a string to lower-case Example: SELECT LOWER("SQL Tutorial is FUN!"); returns sql tutorial is fun!
  16. LPAD: Left-pads a string with another string, to a certain length Example: SELECT LPAD("SQL Tutorial", 20, "ABC"); returns ABCABCABSQL Tutorial
  17. LTRIM: Removes leading spaces from a string Example: SELECT LTRIM(" SQL Tutorial") AS LeftTrimmedString; returns SQL Tutorial
  18. MID: Extracts a substring from a string (starting at any position) Example: SELECT MID("SQL Tutorial", 5, 3) AS ExtractString; returns Tut
  19. 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
  20. REPEAT: Repeats a string as many times as specified Example: SELECT REPEAT("SQL Tutorial", 3); returns SQL TutorialSQL TutorialSQL Tutorial
  21. REPLACE: Replaces all occurrences of a substring within a string, with a new substring Example: SELECT REPLACE("SQL Tutorial", "SQL", "HTML"); returns HTML Tutorial
  22. REVERSE: Reverses a string and returns the result Example: SELECT REVERSE("SQL Tutorial"); returns lairotuT LQS
  23. RIGHT: Extracts a number of characters from a string (starting from right) Example: SELECT RIGHT("SQL Tutorial is cool", 4) AS ExtractString; returns cool
  24. RPAD: Right-pads a string with another string, to a certain length Example: SELECT RPAD("SQL Tutorial", 20, "ABC"); returns SQL TutorialABCABCAB
  25. RTRIM: Removes trailing spaces from a string Example: SELECT RTRIM("SQL Tutorial ") AS RightTrimmedString; returns SQL Tutorial
  26. SPACE: Returns a string of the specified number of space characters Example: SELECT SPACE(10); returns “ “
  27. STRCMP: Compares two strings Example: SELECT STRCMP("SQL Tutorial","SQL Tutorial"); returns 0
  28. SUBSTR: Extracts a substring from a string (starting at any position) Example: SELECT SUBSTR("SQL Tutorial", 5, 3) AS ExtractString; returns Tut
  29. SUBSTRING: Extracts a substring from a string (starting at any position) Example: SELECT SUBSTRING("SQL Tutorial", 5, 3) AS ExtractString; returns Tut
  30. 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
  31. TRIM: Removes leading and trailing spaces from a string Example: SELECT TRIM(" SQL Tutorial ") AS TrimmedString; returns SQL Tutorial
  32. UCASE: Converts a string to upper-case Example: SELECT UCASE("SQL Tutorial is FUN!"); returns SQL TUTORIAL IS FUN!
  33. UPPER: Converts a string to upper-case Example: SELECT UPPER("SQL Tutorial is FUN!"); returns SQL TUTORIAL IS FUN!