Custom Search
 


Knowledge Base
MySQL Tutorials
Using String Functions, Part 3

Here is Part 3 of this tutorial for MySQL String Functions. Here are the links to Part 1 and 2:

  1. Part 1 of MySQL String Functions
  2. Part 2 of MySQL String Functions

Here is the list of MySQL String Functions for Part 3:

No. Name Description Practice
31REPEAT(str,count)Repeat a string the specified number of times

Returns a string consisting of the string str repeated count times. See Practice #31-1.

If count is less than 1, returns an empty string. See Practice #31-2.

Returns NULL if str or count are NULL.

32REPLACE(str,from_str,to_str)Returns the string str with all occurrences of the string from_str replaced by the string to_str.

REPLACE() performs a case-sensitive match when searching for from_str.

#32
33REVERSE(str)Returns the string str with the order of the characters reversed. #33
34RIGHT(str,len)Returns the rightmost len characters from the string str, or NULL if any argument is NULL.#34
35RPAD(str,len,padstr)Returns a string which is formed by right-padding str with the string padstr to a length of len characters. If the length of str is the same as len, the returned value is str right-padded by padstr. See Practice #35-1.

If the length of str is longer than len, the returned value is str shortened by len number of characters. See Practice #35-2.

If the length of str is shorter than len, the returned value is an extended version of str where str is right-padded by padstr until it reaches the length of len characters. See Practice #35-3.

36RTRIM(str)Returns the string str with trailing space characters removed.#36
37SOUNDEX(str)Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. See Practice #37-1.

A standard soundex string is four characters long alphanumeric code, but the SOUNDEX() function can return an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. See Practice #37-2.

All non-alphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.

Limitations in MySQL version 5.1

1. This function, as of MySQL version 5.1, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results.

2. This function is not guaranteed to provide consistent results with strings that use multi-byte character sets, including utf-8.

38SPACE(N)Returns a string consisting of N space characters.#38
39STRCMP(expr1,expr2)STRCMP returns 0 if the two strings are the same. See Practice #39-1.

Returns -1 if the first string is smaller than the second string based on the current sort order. See Practice #39-2.

Returns 1 if the first string is greater than the second string based on the current sort order. See Practice #39-3.

40SUBSTR(str,pos[,len]))SUBSTR is a synonym for SUBSTRING.
41SUBSTRING(str,pos[,len])Returns a substring starting from the specified position. SUBSTRING is a synonym for MID.

There are two main forms of using SUBSTRING:

1. SUBSTRING(str,pos[,len])

In this form, len is an optional argument. If it's omitted, SUBSTRING returns a substring from string str starting at position pos. See Practice #41-1.

If len presents, SUBSTRING returns a substring of len characters long from string str, starting at position pos. See Practice #41-2.

2. SUBSTRING(str FROM pos [FOR len])

In this form, FOR len is an optional argument. If it's omitted, SUBSTRING returns a substring from string str starting at position pos. See Practice #41-3.

If FOR len presents, SUBSTRING returns a substring of len characters long from string str, starting at position pos. See Practice #41-4.

Using negative value for pos

It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. See Practice #41-5.

A negative value may be used for pos in any of the two forms of this function.

42SUBSTRING_INDEX(str,delim,count)Returns the substring from string str before count occurrences of the delimiter delim.

If count is positive, everything to the left of the final delimiter (counting from the left) is returned. See Practice #42-1.

If count is negative, everything to the right of the final delimiter (counting from the right) is returned. See Practice #42-2.

SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

43TRIM(str)Remove leading and/or trailing spaces.

There are 5 different ways to use TRIM() function:

1. TRIM(str)

Trim both leading and trailing spaces. See Practice #43-1.

2. TRIM(LEADING remstr FROM str)

Only trim leading spaces. See Practice #43-2.

3. TRIM(TRAILING remstr FROM str)

Only trim trailing spaces. See Practice #43-3.

4. TRIM(BOTH remstr FROM str)

Trim both leading and trailing spaces. See Practice #43-4.

5. TRIM(remstr FROM str)

Trim both leading and trailing spaces. See Practice #43-5.

44UCASE(str)UCASE is synonym for UPPER function.
45UNHEX(str)UNHEX interprets each pair of hexadecimal digits in the argument as a number and converts it to the character represented by the number.

UNHEX performs the inverse operation of HEX().

#45
46UPPER(str)Returns the string str with all characters changed to uppercase according to the current character set mapping. The default is latin1 (cp1252 West European).

UPPER is synonym for UCASE function.

#46


Practice #31-1: Using REPEAT string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
REPEAT(str,count)
 
This query repeats the word Geeks 3 times.
*/
SELECT REPEAT('Geeks', 3);

Query result set - 1 row returned:
Using REPEAT function

Practice #31-2: Using REPEAT string function with negative number

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
REPEAT(str,count)
 
This query returns empty string because
the count is a negative number.
*/
SELECT REPEAT('Geeks', -2);

Query result set - 1 row returned:
Using REPEAT function with negative number

Practice #32: Using REPLACE string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
Replaces all occurrences of the string Geeks
with the string Search.
*/
SELECT REPLACE('GeeksEngine', 'Geeks', 'Search');

Query result set - 1 row returned:
Using REPLACE string function

Practice #33: Using REVERSE string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
Reverse the string GeeksEngine.
*/
SELECT REVERSE('GeeksEngine');

Query result set - 1 row returned:
Using REVERSE string function

Practice #34: Using RIGHT string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
Returns the rightmost 6 characters 
from the string GeeksEngine.
*/
SELECT RIGHT('GeeksEngine', 6);

Query result set - 1 row returned:
Using RIGHT string function

Practice #35-1: Using RPAD function with same len and pos value

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
RPAD(str,len,padstr)
 
Right pad string web with string site
to 7 characters long, which is the exact 
length of string website.
*/
SELECT RPAD('web',7,'site');

Query result set - 1 row returned:
Using RPAD function with same len and pos value

Practice #35-2: Using RPAD() function with shorter len value

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
RPAD(str,len,padstr)
 
String web is shortened to we because its length 
(3 characters) is greater than len (2 characters).
*/
SELECT LPAD('web',2,'site');

Query result set - 1 row returned:
Using RPAD() function with shorter len value

Practice #35-3: Using RPAD() function with longer len value

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
RPAD(str,len,padstr)
 
String web is right padded with string site
until the len number of characters is reached.
This is because the length of string web
(3 characters) is less than len (11 characters).
*/
SELECT RPAD('web',11,'site');

Query result set - 1 row returned:
Using RPAD() function with longer len value

Practice #36: Using RTRIM string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

-- Remove trailing spaces
SELECT RTRIM('Geeks   ');

Query result set - 1 row returned:
Using RTRIM string function

Practice #37-1: Using SOUNDEX function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
The string World and Word sound similar and 
the two queries return similar soundex string values.
 
Query 1 returns W643 and Query 2 returns W630
*/
SELECT SOUNDEX('World');
 
SELECT SOUNDEX('Word');

Query result set - 1 row returned:
Using SOUNDEX function

Query result set - 1 row returned:
Using SOUNDEX function

Practice #37-2: Using SOUNDEX function and LENGTH function to get standard soundex string

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
SOUNDEX function does not return the standard 
soundex string which a four characters long 
alphanumeric code. We use SUBSTRING() to get a 
standard soundex string of the first four characters.
 
In the following two queries, the string World Red Cross 
has a soundex string of W64363262. SUBSTRING() is used 
to get the standard soundex string - W643
*/
SELECT SOUNDEX('World Red Cross');
 
SELECT SUBSTRING(SOUNDEX('World Red Cross'), 1, 4);
 
/*
In the following two queries, the string Word Red Grass 
has a soundex string of W6363262. SUBSTRING() is used 
to get the standard soundex string - W636
*/
SELECT SOUNDEX('Word Red Grass');
 
SELECT SUBSTRING(SOUNDEX('Word Red Grass'), 1, 4);

Query result set - 1 row returned for non-standard soundex string:
SOUNDEX function returns non-standard soundex string

Query result set - 1 row returned for standard soundex string:
Using SOUNDEX function and LENGTH function to get standard soundex string

Query result set - 1 row returned for non-standard soundex string:
SOUNDEX function returns non-standard soundex string

Query result set - 1 row returned for standard soundex string:
Using SOUNDEX function and LENGTH function to get standard soundex string

Practice #38: Using SPACE string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
In this query, we added 5 space characters in between
string Geeks and Engine.
*/
SELECT CONCAT('Geeks', SPACE(5), 'Engine') AS show_space;

Query result set - 1 row returned:

Practice #39-1: Using STRCMP string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
Returns 0 when the two strings are the same.
 
These two queries return the same result because
the default comparison behavior is case insensitive.
*/
SELECT STRCMP('world', 'world');
 
SELECT STRCMP('world', 'WORLD');

Query result set - 1 row returned:
Using STRCMP string function

Query result set - 1 row returned:
Using STRCMP string function

Practice #39-2: Using STRCMP string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
Returns 1 if the first string is bigger than 
the second string based on the current sort order.
*/
SELECT STRCMP('worldcup', 'world');

Query result set - 1 row returned:
Using STRCMP string function

Practice #39-3: Using STRCMP string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
Returns -1 if the first string is smaller than 
the second string based on the current sort order.
*/
SELECT STRCMP('world', 'worldcup');

Query result set - 1 row returned:
Using STRCMP string function

Practice #41-1: Using SUBSTRING function without len argument

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
SUBSTRING(str,pos)
 
This query returns a substring from string 
GeeksEngine, starting at position 6.
*/
SELECT SUBSTRING('GeeksEngine',6);

Character index position for string GeeksEngine:

1 2 3 4 5 6 7 8 9 10 11
G e e k s E n g i n e


Query result set - 1 row returned:
Using SUBSTRING function without len argument

Practice #41-2: Using SUBSTRING function with len argument

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
SUBSTRING(str,pos,len)
 
This query returns a substring of 9 characters long 
from string GeeksEngine, starting at position 8.
*/
SELECT SUBSTRING('GeeksEngine',8,9);

Character index position for string GeeksEngine:

1 2 3 4 5 6 7 8 9 10 11
G e e k s E n g i n e


Query result set - 1 row returned:
Using SUBSTRING function with len argument

Practice #41-3: Using SUBSTRING(str FROM pos)

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
SUBSTRING(str FROM pos)
 
This query returns a substring from string 
GeeksEngine, starting at position 6.
*/
SELECT SUBSTRING('GeeksEngine' FROM 6);

Character index position for string GeeksEngine:

1 2 3 4 5 6 7 8 9 10 11
G e e k s E n g i n e


Query result set - 1 row returned:
SUBSTRING(str FROM pos)

Practice #41-4: Using SUBSTRING(str FROM pos FOR len)

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
SUBSTRING(str FROM pos FOR len)
 
This query returns a substring of 9 characters long 
from string GeeksEnngine, starting at position 8.
*/
SELECT SUBSTRING('GeeksEngine' FROM 8 FOR 9);

Character index position for string GeeksEngine:

1 2 3 4 5 6 7 8 9 10 11
G e e k s E n g i n e


Query result set - 1 row returned:
SUBSTRING(str FROM pos FOR len)

Practice #41-5: Using SUBSTRING with negative pos value

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
If pos value is negative number, the beginning 
of the substring is the number of pos characters 
from the end of the string, rather than the beginning.
 
This query returns a substring of 3 characters long 
from string GeeksEngine, starting at position 6 counting
from right to left.
*/
SELECT SUBSTRING('GeeksEngine', -6, 3);

Character index position for string GeeksEngine:

11 10 9 8 7 6 5 4 3 2 1
G e e k s E n g i n e


Query result set - 1 row returned:
Using SUBSTRING with negative pos value

Practice #42-1: Using SUBSTRING_INDEX string function for positive count value

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
SUBSTRING_INDEX(str,delim,count)
 
Because the count is positive, everything to the left 
of the last delimiter (counting from the left) is returned.
 
This query returns www.GeeksEngine which is the part on
the left hand side of the second dot (counting from the left).
*/
SELECT SUBSTRING_INDEX('www.GeeksEngine.com', '.', 2);

Query result set - 1 row returned:
Using SUBSTRING_INDEX string function for positive count value

Practice #42-2: Using SUBSTRING_INDEX string function for negative count value

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
SUBSTRING_INDEX(str,delim,count)
 
Because the count is negative, everything to the right 
of the last delimiter (counting from the right) is returned.
 
This query returns GeeksEngine.com which is the part on
the right hand side of the first dot (counting from the left).
*/
SELECT SUBSTRING_INDEX('www.GeeksEngine.com', '.', -2);

Query result set - 1 row returned:
Using SUBSTRING_INDEX string function for negative count value

Practice #43-1: Using TRIM string function to trim both leading and trailing spaces

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
This query trims both leading and trailing
spaces for string '  Engine   ' and then
concatenate with string Geeks.
*/
SELECT CONCAT('Geeks', TRIM('  Engine   '));

Query result set - 1 row returned:
Using TRIM string function to trim both leading and trailing spaces

Practice #43-2: Using TRIM string function to trim leading characters

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
This query trims leading xx in string xxxGeeksEngine
and the result is xGeeksEngine. Note that if we trim
single character x, all leading xxx will be removed.
*/
SELECT TRIM(LEADING 'xx' FROM 'xxxGeeksEngine');

Query result set - 1 row returned:
Using TRIM string function to trim leading characters

Practice #43-3: Using TRIM string function to trim trailing characters

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
This query trims trailing xx in string GeeksEnginexxx
and the result is GeeksEnginex. Note that if we trim
single character x, all xxx at the end will be removed.
*/
SELECT TRIM(TRAILING 'xx' FROM 'GeeksEnginexxx');

Query result set - 1 row returned:
Using TRIM string function to trim trailing characters

Practice #43-4: Using TRIM to remove both leading and trailing characters

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
This query trims both leading xx and trailing xx
in string xxxGeeksEnginexx and the result is 
xGeeksEngine. Note that if we trim single character x, 
all leading xxx and trailing xx will be removed.
*/
SELECT TRIM(BOTH 'xx' FROM 'xxxGeeksEnginexx');

Query result set - 1 row returned:
Using TRIM to remove both leading and trailing characters

Practice #43-5: Using TRIM to remove both leading and trailing characters without specifying BOTH

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

/*
If no keyword BOTH is specified, removing 
both leading xx and trailing xx is assumed.
 
This query returns the same result as the query
in Practice 43-4 above.
*/
SELECT TRIM('xx' FROM 'xxxGeeksEnginexx');

Query result set - 1 row returned:
Using TRIM to remove both leading and trailing characters without specifying BOTH

Practice #45: Using UNHEX string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

-- Convert hexadecimal digits to its characters.
SELECT UNHEX('4765656B73');
 
-- This query returns the same result as the query above
SELECT 0x4765656B73;

Query result set - 1 row returned:
Using UNHEX string function

Query result set - 1 row returned:
Using UNHEX string function

Practice #46: Using UPPER string function

In Firefox (not IE), copy and paste the following SQL to your SQLyog query window. To execute a query, move your cursor anywhere inside the query and then press F9. Note that the SQL needs to end with semi-colon.

-- Convert to upper-case
SELECT UPPER('geeksengine');

Query result set - 1 row returned:
Using UPPER string function





  1. Next tutorial: Date and Time Functions, Part 1

  2. Back to Section Index: Single Row Functions

  3. Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

Copyright © 2010 GeeksEngine.com. All Rights Reserved.

This website is hosted by LunarPages.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.
 
Home | Kung Fu Timer | Feedback | Terms of Use | Privacy Policy