Use MySQL String Functions, Part 3

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:

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

No.	Name	Description	Practice
31	REPEAT(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.
32	REPLACE(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
33	REVERSE(str)	Returns the string str with the order of the characters reversed.	#33
34	RIGHT(str,len)	Returns the rightmost len characters from the string str, or NULL if any argument is NULL.	#34
35	RPAD(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.
36	RTRIM(str)	Returns the string str with trailing space characters removed.	#36
37	SOUNDEX(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.
38	SPACE(N)	Returns a string consisting of N space characters.	#38
39	STRCMP(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.
40	SUBSTR(str,pos[,len]))	SUBSTR is a synonym for SUBSTRING.
41	SUBSTRING(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.
42	SUBSTRING_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.
43	TRIM(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.
44	UCASE(str)	UCASE is synonym for UPPER function.
45	UNHEX(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
46	UPPER(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. Note that the SQL needs to end with semi-colon if you have multiple queries in the query window. Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.

/*
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

/*
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

/*
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

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

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

Practice #34: Using RIGHT string function

/*
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

/*
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

/*
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

/*
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

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

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

Practice #37-1: Using SOUNDEX function

/*
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

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

/*
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 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

/*
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

Practice #39-2: Using STRCMP string function

/*
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

/*
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