MySQL String Functions

This page shows you the most commonly used MySQL string functions that allow you to manipulate character string data effectively.

NameDescription
CONCATConcatenate two or more strings into a single string.
CONCAT_WSReturn a single string by concatenating multiple strings separated by a specified separator.
INSTRReturn the position of the first occurrence of a substring in a string.
LENGTHGet the length of a string in bytes.
CHAR_LENGTHReturn the length of a string measured in characters.
LEFTGet a specified number of leftmost characters from a string.
LOWERReturn a string converted to lowercase.
LOCATEReturn the position of a substring within a given string starting at a specified position.
LTRIMRemove all leading spaces from a string.
REPLACEReplace all occurrences of a substring in a string with a new one.
REPEATRepeat a string a specified number of time.
REVERSEReverse a string.
RIGHTGet a specified number of rightmost characters from a string.
RTRIMRemove all trailing spaces from a string.
SUBSTRINGExtract a substring starting from a position with a specific length.
SUBSTRING_INDEXReturn a substring from a string before a specified number of occurrences of a delimiter.
TRIMRemove unwanted characters from a string.
FIND_IN_SETFind a string within a comma-separated list of strings.
FORMATFormat a number with a specific locale, rounded to the number of decimals.
UPPERConvert a string to uppercase.

Concatenation Functions

  • CONCAT(): Combines two or more strings into a single string.
  • CONCAT_WS(): Combines multiple strings into a single string with a specified separator.

Substring Functions

  • SUBSTRING(): Extracts a substring from a given string.
  • SUBSTRING_INDEX(): Extracts a substring from a string using a delimiter.
  • LEFT(): Returns a specified number of characters from the beginning of a string.
  • RIGHT(): Returns a specified number of characters from the end of a string.
  • MID(): Extracts a substring from the middle of a string. The MID() function is a synonym for SUBSTRING().

Searching and Locating Functions

  • LOCATE(): Finds the position of a substring within a string.
  • POSITION(): Finds the position of a substring. The POSITION() is a synonym for the LOCATE() function.
  • INSTR(): Another function for finding the position of a substring.

Case Conversion Functions

  • UPPER(): Converts a string to uppercase.
  • LOWER(): Converts a string to lowercase.

Character Manipulation Functions

  • REPLACE(): Replaces all occurrences of a substring in a string.
  • TRIM(): Removes leading and trailing spaces from a string.
  • LTRIM(): Removes leading spaces from a string.
  • RTRIM(): Removes trailing spaces from a string.
  • REPEAT(): Repeats a string a specified number of times.
  • REVERSE(): Reverses the characters in a string.
  • INSERT(): Replaces a substring within a string with a new substring.

Whitespace Functions

  • SPACE(): Returns a string consisting of spaces.
  • ASCII(): Returns the ASCII value of the leftmost character of a string.
  • CHAR(): Converts an ASCII value to a character.

Length and Count Functions

  • LENGTH(): Returns the length of a string in bytes.
  • CHAR_LENGTH(): Returns the length of a string in characters.
  • OCTET_LENGTH(): Returns the length of a string in bytes.
  • BIT_LENGTH(): Returns the length of a string in bits.
  • CHARACTER_LENGTH(): Returns the length of a string in characters.
  • BIT_COUNT(): Counts the number of bits in a binary string.
  • STRCMP(): Compares two strings and returns their relative order.

Padding string functions

  • LPAD() – Left-pads a string with a set of characters to a specified length.
  • RPAD() – Right-pads a string with a set of characters to a specified length.