String functions

Function

Description

Example

ASCII(s)

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

Execute the following statement to return the ASCII code of the first character in the "CustomerName" string:

SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;

CHAR_LENGTH(s)

Returns the number of characters in the s string.

Execute the following statement to return the number of characters in the "RUNOOB" string:

SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;

CHARACTER_LENGTH(s)

Returns the number of characters in the s string.

Execute the following statement to return the number of characters in the "RUNOOB" string:

SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;

CONCAT(s1,s2...sn)

Concatenates multiple strings.

Execute the following statement to concatenate multiple strings:

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

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

This function is a special form of the CONCAT(s1,s2,...) function. This function concatenates multiple strings. The x argument is the separator for the rest of the arguments.

Execute the following statement to concatenate multiple strings and separate the strings with the first argument:

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

FIELD(s,s1,s2...)

Returns the position of the s string in a list of strings that include the s1 and s2 strings.

Execute the following statement to return the position of the "c" string in the list of strings. The string list is composed of the "a" string, the "b" string, the "c" string, the "d" string, and the "e" string.

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

FIND_IN_SET(s1,s2)

Returns the position of the s1 substring within the s2 string.

Execute the following statement to return the position of the "c" substring within the string that is composed of the "a", "b", "c", "d", and "e" substrings:

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

FORMAT(x,n)

Returns the number x in the '#,###.##' format. The function rounds the number x to n decimal places.

Execute the following statement to return 250500.5634 in the '#,###.##' format:

SELECT FORMAT(250500.5634, 2);

The following result is returned:

-- 250,500.56

INSERT(s1,x,len,s2)

Replaces a substring of the s1 string with the s2 string. x specifies the position of the first character to replace in the s1 string. len specifies the length of the replaced substring.

Execute the following statement to replace the first six characters of the "google.com" string with the "runoob" string:

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

The following result is returned:

-- runoobe.com

LOCATE(s1,s)

Returns the position of the first occurrence of the s1 substring in the s string.

• Execute the following statement to return the position of the first occurrence of the "st" substring in the "myteststring" string:

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

  The following result is returned:

  -- 5

• Execute the following statement to return the position of the first occurrence of the "b" substring in the "abc" string:

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

  The following result is returned:

  -- 2

LCASE(s)

Returns the s string in lowercase.

Execute the following statement to return the "RUNOOB" string in lowercase:

SELECT LCASE('RUNOOB');

The following result is returned:

-- runoob

LEFT(s,n)

Returns the first n characters of the s string.

Execute the following statement to return the first two characters of the "runoob" string:

SELECT LEFT('runoob',2);

The following result is returned:

-- ru

LOWER(s)

Returns the s string in lowercase.

Execute the following statement to return the "RUNOOB" string in lowercase:

SELECT LOWER('RUNOOB');

-- runoob

LPAD(s1,len,s2)

Left-pads the s1 string with the s2 string to a length of len characters.

Execute the following statement to left-pad the "abc" string with the "xx" string:

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

The following result is returned:

-- xxabc

LTRIM(s)

Removes the leading space of the s string.

Execute the following statement to remove the leading space of the "RUNOOB" string:

SELECT LTRIM(" RUNOOB") AS LeftTrimmedString;

The following result is returned:

-- RUNOOB

MID(s,n,len)

Extracts a substring from the s string. n specifies the position of the first occurrence of the substring in the s string. len specifies the length of the substring. This function is equivalent to SUBSTRING(s,n,len).

Execute the following statement to extract a substring from the "RUNOOB" string. The substring starts at the second character of the "RUNOOB" string and contains three characters.

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

The following result is returned:

-- UNO

POSITION(s1 IN s)

Returns the position of the first occurrence of the s1 substring in the s string.

Execute the following statement to return the position of the first occurrence of the "b" substring in the "abc" string:

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

The following result is returned:

-- 2

REPEAT(s,n)

Repeats the s string n times.

Execute the following statement to repeat the "runoob" string three times:

SELECT REPEAT('runoob',3);

The following result is returned:

-- runoobrunoobrunoob

REPLACE(s,s1,s2)

Replaces the s1 substring of the s string with the s2 string.

Execute the following statement to replace the "a" character in the "abc" string with the "x" character:

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

The following result is returned:

--xbc

REVERSE(s)

Reverses the characters in the s string.

Execute the following statement to reverse the characters in the "abc" string:

SELECT REVERSE('abc');

The following result is returned:

-- cba

RIGHT(s,n)

Returns the last n characters of the s string.

Execute the following statement to return the last two characters of the "runoob" string:

SELECT RIGHT('runoob',2);

The following result is returned:

-- ob

RPAD(s1,len,s2)

Right-pads the s1 string with the s2 string to a length of len characters.

Execute the following statement to right-pad the "abc" string with the "xx" string:

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

The following result is returned:

-- abcxx

RTRIM(s)

Removes the trailing spaces of the s string.

Execute the following statement to remove the trailing spaces of the "RUNOOB" string:

SELECT RTRIM("RUNOOB") AS RightTrimmedString;

The following result is returned:

-- RUNOOB

SPACE(n)

Returns a string that consists of n spaces.

Execute the following statement to return a string that consists of 10 spaces:

SELECT SPACE(10);

STRCMP(s1,s2)

Compares the s1 and s2 strings based on ASCII values. If the ASCII values of the strings are the same, 0 is returned. If the ASCII value of the s1 string is larger than that of the s2 string, 1 is returned. If the ASCII value of the s1 string is smaller than that of the s2 string, -1 is returned.

Execute the following statement to compare the "runoob" string and the "runoob" string based on ASCII values:

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

The following result is returned:

-- 0

SUBSTR(s, start, length)

Extracts a substring from the s string. start specifies the position of the first occurrence of the substring in the s string. length specifies the length of the substring.

Execute the following statement to extract a substring from the "RUNOOB" string. The substring starts at the second character of the "RUNOOB" string and contains three characters.

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

The following result is returned:

-- UNO

SUBSTRING(s, start, length)

Extracts a substring from the s string. start specifies the position of the first occurrence of the substring in the s string. length specifies the length of the substring.

Execute the following statement to extract a substring from the "RUNOOB" string. The substring starts at the second character of the "RUNOOB" string and contains three characters.

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

The following result is returned:

-- UNO

SUBSTRING_INDEX(s, delimiter, number)

Returns a substring of the s string. The delimiter argument specifies the delimiter in the string.

If number is positive, the function locates the 'number'th delimiter by counting from the left and returns all characters to the left of this delimiter.

If number is negative, the function locates the 'number'th delimiter by counting from the right and returns all characters to the right of this delimiter. 'number' is the absolute value of the number argument.

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

  The following result is returned:

  -- a

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

  The following result is returned:

  -- b

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

  The following result is returned:

  -- c

TRIM(s)

Removes the leading spaces and trailing spaces from the s string.

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

SELECT TRIM('RUNOOB') AS TrimmedString;

UCASE(s)

Returns the s string in uppercase.

Execute the following statement to return the "runoob" string in uppercase:

SELECT UCASE("runoob");

The following result is returned:

-- RUNOOB

UPPER(s)

Returns the s string in uppercase.

Execute the following statement to return the "runoob" string in uppercase:

SELECT UPPER("runoob");

The following result is returned:

-- RUNOOB

Function

Description

LOAD_FILE()

Loads a file.

MATCH

Performs a full-text search.

SOUNDS LIKE

Compares strings that sound the same.