String functions

Function name

Description

Example

ASCII(s)

Returns the ASCII code of the first character in string s.

Returns the ASCII code of the first character in the CustomerName field:

SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;

CHAR_LENGTH(s)

Returns the number of characters in string s.

Returns the number of characters in the string "RUNOOB":

SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;

CHARACTER_LENGTH(s)

Returns the number of characters in string s.

Returns the number of characters in the string "RUNOOB":

SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;

CONCAT(s1,s2...sn)

Concatenates multiple strings, such as s1 and s2, into a single string.

Concatenates multiple strings:

SELECT CONCAT("SQL ", "Runoob ", "Google ", "Facebook") AS ConcatenatedString;

CONCAT_WS(x, s1,s2...sn)

Similar to the CONCAT(s1,s2,...) function, but adds x as a separator between each string.

Concatenates multiple strings and adds a separator:

SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") AS ConcatenatedString;

FIELD(s,s1,s2...)

Returns the index position of string s in the string list (s1,s2,...).

Returns the position of the string "c" in the list of values:

SELECT FIELD("c", "a", "b", "c", "d", "e");

FIND_IN_SET(s1,s2)

Returns the position of the first occurrence of string s1 in string s2.

Returns the position of the substring c in a string:

SELECT FIND_IN_SET("c", "a,b,c,d,e");

FORMAT(x,n)

Formats the number x as "#,###.##". The function rounds x to n decimal places.

Formats a number as "#,###.##":

SELECT FORMAT(250500.5634, 2);

Returns:

-- 250,500.56

INSERT(s1,x,len,s2)

Replaces a substring of length len in string s1, starting at position x, with string s2.

Replaces the first 6 characters of the string with "runoob":

SELECT INSERT("example.com", 1, 6, "runoob");

Returns:

-- runoobe.com

LOCATE(s1,s)

Returns the starting position of s1 in string s.

• Retrieves the position of "st" in the string "myteststring":

  SELECT LOCATE('st','myteststring');

  Returns:

  -- 5

• Returns the position of "b" in the string "abc":

  SELECT LOCATE('b', 'abc');

  Returns:

  -- 2

LCASE(s)

Converts all characters in string s to lowercase.

Converts the string "RUNOOB" to lowercase:

SELECT LCASE('RUNOOB');

Returns:

-- runoob

LEFT(s,n)

Returns the first n characters of string s.

Returns the first two characters of the string "runoob":

SELECT LEFT('runoob',2);

Returns:

-- ru

LOWER(s)

Converts all characters in string s to lowercase.

Converts the string "RUNOOB" to lowercase:

SELECT LOWER('RUNOOB');

-- runoob

LPAD(s1,len,s2)

Pads the beginning of string s1 with string s2 until the string reaches the length len.

Pads the beginning of the string "abc" with the string "xx":

SELECT LPAD('abc',5,'xx')

Returns:

-- xxabc

LTRIM(s)

Removes leading spaces from string s.

Removes the leading spaces from the string " RUNOOB":

SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;

Returns:

-- RUNOOB

MID(s,n,len)

Extracts a substring of length len from string s, starting at position n. This is a synonym for SUBSTRING(s,n,len).

Extracts 3 characters from the string "RUNOOB", starting from the second position:

SELECT MID("RUNOOB", 2, 3) AS ExtractString;

Returns:

-- UNO

POSITION(s1 IN s)

Returns the starting position of s1 in string s.

Returns the position of "b" in the string "abc":

SELECT POSITION('b' in 'abc');

Returns:

-- 2

REPEAT(s,n)

Repeats string s n times.

Repeats the string "runoob" three times:

SELECT REPEAT('runoob',3);

Returns:

-- runoobrunoobrunoob

REPLACE(s,s1,s2)

Replaces all occurrences of string s1 in string s with string s2.

Replaces the character "a" with "x" in the string "abc":

SELECT REPLACE('abc','a','x');

Returns:

--xbc

REVERSE(s)

Reverses the order of the characters in string s.

Reverses the order of the characters in the string "abc":

SELECT REVERSE('abc');

Returns:

-- cba

RIGHT(s,n)

Returns the last n characters of string s.

Returns the last two characters of the string "runoob":

SELECT RIGHT('runoob',2);

Returns:

-- ob

RPAD(s1,len,s2)

Pads the end of string s1 with string s2 until the string reaches the length len.

Pads the end of the string "abc" with the string "xx":

SELECT RPAD('abc',5,'xx');

Returns:

-- abcxx

RTRIM(s)

Removes trailing spaces from string s.

Removes the trailing spaces from the string "RUNOOB ":

SELECT RTRIM("RUNOOB") AS RightTrimmedString;

Returns:

-- RUNOOB

SPACE(n)

Returns n space characters.

Returns 10 space characters:

SELECT SPACE(10);

STRCMP(s1,s2)

Compares strings s1 and s2. Returns 0 if s1 is equal to s2, 1 if s1 is greater than s2, and -1 if s1 is less than s2.

Compares two strings:

SELECT STRCMP("runoob", "runoob");

Returns:

-- 0

SUBSTR(s, start, length)

Extracts a substring of length `length` from string s, starting at position `start`.

Extracts 3 characters from the string "RUNOOB", starting from the second position:

SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString;

Returns:

-- UNO

SUBSTRING(s, start, length)

Extracts a substring of length `length` from string s, starting at position `start`.

Extracts 3 characters from the string "RUNOOB", starting from the second position:

SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString;

Returns:

-- UNO

SUBSTRING_INDEX(s, delimiter, number)

Returns the substring of string s that follows the numberth occurrence of the separator.

If number is positive, this function returns the characters that appear before the character at that position.

If number is a negative number, this function returns the substring starting from the position that is the absolute value of number characters from the right end of the string.

• SELECT SUBSTRING_INDEX('a*b','*',1);

  Returns:

  -- a

• SELECT SUBSTRING_INDEX('a*b','*',-1);

  Returns:

  -- b

• SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1);

  Returns:

  -- c

TRIM(s)

Removes leading and trailing spaces from string s.

Removes the leading and trailing spaces from the string " RUNOOB ":

SELECT TRIM('RUNOOB') AS TrimmedString;

UCASE(s)

Converts a string to uppercase.

Converts the string "runoob" to uppercase:

SELECT UCASE("runoob");

Returns:

-- RUNOOB

UPPER(s)

Converts a string to uppercase.

Converts the string "runoob" to uppercase:

SELECT UPPER("runoob");

Returns:

-- RUNOOB

Function name

Description

LOAD_FILE()

Loads a file.

MATCH

Performs a full-text search.

SOUNDS LIKE

Compares strings based on their sound.