Use MySQL String Functions, Part 2

Using String Functions, Part 2

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

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

No.	Name	Description	Practice
16	INSTR(str,substr)	Return the index position of the first occurrence of substr inside str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.	#16
17	LCASE(str)	Returns the string str with all characters changed to lowercase according to the current character set mapping. The default is latin1 (cp1252 West European). LCASE() is synonym for LOWER()	#17
18	LEFT(str,len)	Returns the leftmost len number of characters from the string str, or NULL if any argument is NULL.	#18
19	LENGTH(str)	Returns the length of the string str, measured in bytes. See Practice #19-1 A multi-byte character counts as multiple bytes. This means that for a string containing two three-byte characters, LENGTH() returns 6, whereas CHAR_LENGTH() returns 2. See Practice #19-2 LENGTH is A synonym for OCTET_LENGTH. See CHAR_LENGTH() function to compare how it's different to LENGTH() function in terms of handling the length for multi-byte characters.
20	LOAD_FILE(file_name)	Reads the file and returns the file content as a string. To use this function, the file must be located on the server host, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes. If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL. As of MySQL 5.1.6, the character_set_filesystem system variable controls interpretation of filenames that are given as literal strings.	#20
21	LOCATE(substr,str[,pos])	Returns the position of the first occurrence of substr inside str. If pos is omitted, the search starts at position 1. See Practice #21-1. If pos presents, the search starts at position specified by pos. See Practice #21-2. LOCATE() is the same as the two-argument form of INSTR(), except that the order of the arguments is reversed. LOCATE is a synonym for POSITION.
22	LOWER(str)	Same as LCASE() which returns the string str with all characters changed to lowercase according to the current character set mapping. The default is latin1 (cp1252 West European).
23	LPAD(str,len,padstr)	Returns a string which is formed by left-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 left-padded by padstr. See Practice #23-1. If the length of str is longer than len, the returned value is str shortened by len number of characters. See Practice #23-2. If the length of str is shorter than len, the returned value is an extended version of str where str is left-padded by padstr until it reaches the length of len characters. See Practice #23-3.
24	LTRIM(str)	Returns the string str with leading space characters removed.	#24
25	MAKE_SET(bits,str1,str2,...)	Returns a set value (a string containing substrings separated by "," characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result.	#25
26	MID(str,pos,len)	Return a substring starting from the specified position. MID is a synonym for SUBSTRING.
27	OCTET_LENGTH(str)	OCTET_LENGTH is A synonym for LENGTH.
28	ORD(str)	Returns character code for leftmost character of the argument str. 1. For multi-byte characters. If the leftmost character of the string str is a multi-byte character, ORD returns the decimal character code for that character. See Practice #28-1. 2. For single-byte characters such as English. If the leftmost character is not a multi-byte character, ORD() returns the same value as the ASCII() function. See Practice #28-2.
29	POSITION(substr IN str)	POSITION is a synonym for LOCATE but has a subtle difference in the format of arguments passed into it. POSITION(substr IN str) LOCATE(substr,str)
30	QUOTE(str)	Returns a string that contains the escape character backslash (\) and single quotes in the result.	#30

Practice #16: Using INSTR 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.

/*
This query returns the index position of 
the first occurrence of string Excel 
inside string MSExcelOfficeExcel. 
*/
SELECT INSTR('MSExcelOfficeExcel', 'Excel');

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

Character index position for string MSExcelOfficeExcel:

1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18
M	S	E	x	c	e	l	O	f	f	i	c	e	E	x	c	e	l

Practice #17: Using LCASE() string function

-- Query 1: this query converts the string to lower case.
SELECT LCASE('GeeksEngine');
 
-- Query 2: returns the same result as Query 1.
SELECT LOWER('GeeksEngine');

Query result set - 1 row returned:
Using LCASE() string function

Practice #18: Using LEFT string function

-- Returns 5 characters counting from the left.
SELECT LEFT('GeeksEngine', 5);

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

Practice #19-1: Using LENGTH() string function

/*
Count how many bytes in the string. English characters 
use 1 byte per character. So length of string GeeksEngine 
is 11 bytes (or 11 characters).
*/
SELECT LENGTH('GeeksEngine');

Query result set - 1 row returned:
Using LENGTH() string function

Practice #19-2: Using LENGTH() string function for multi-byte characters

/*
Count how many bytes in the string. Chinese
characters use 3 bytes per character. So 
the length of the string 汉字 is 6 bytes.
*/
SELECT LENGTH('字');

Query result set - 1 row returned:
Using LENGTH() string function for multi-byte characters

Practice #20: Using LOAD_FILE() string function

/*
Read the content of MySQL ini file.
*/
SELECT LOAD_FILE('/Program Files/MySQL/MySQL Server 5.0/my.ini');

Query result set - 1 row returned in Text view:
Using LOAD_FILE() string function to load MySQL my.ini file

Practice #21-1: Using LOCATE string function with position number omitted

/*
LOCATE(substr,str)
 
When pos number is omitted, the search 
starts from position 1.
*/
SELECT LOCATE('engine', 'GeeksEngine');

Query result set - 1 row returned:
Using LOCATE string function with position number omitted

Practice #21-2: Using LOCATE string function with position number specified

/*
LOCATE(substr,str,pos)
 
When position number is specified, the search 
starts from position number. In this query,
search starts from position number 8.
*/
SELECT LOCATE('engine', 'GeeksEngineEngine', 8);

Query result set - 1 row returned:
Using LOCATE string function with position number specified

Practice #23-1: Using LPAD() function with same len and pos value

/*
LPAD(str,len,padstr)
 
Left pad string Engine with string Geeks
to 11 characters long, which is the exact 
length of GeeksEngine.
*/
SELECT LPAD('Engine',11,'Geeks');

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

Practice #23-2: Using LPAD() function with shorter len value

/*
LPAD(str,len,padstr)
 
String Engine is shortened because its length 
(6 characters) is greater than len (4 characters).
*/
SELECT LPAD('Engine',4,'Geeks');

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

Practice #23-3: Using LPAD() function with longer len value

/*
LPAD(str,len,padstr)
 
String Engine is left padded with Geeks
until the len number of characters is reached.
This is because the length of string Engine
(6 characters) is less than len (15 characters).
*/
SELECT LPAD('Engine',15,'Geeks');

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

Practice #24: Using LTRIM function to remove leading spaces

-- Remove leading spaces
SELECT LTRIM('  Geeks');

Query result set - 1 row returned:
Using LTRIM function to remove leading spaces

Practice #25: Using MAKE_SET string function

SELECT MAKE_SET(1 | 4,'hello','nice','world');

Query result set - 1 row returned:
Using MAKE_SET function

Practice #28-1: Using ORD function for multi-byte characters

/*
Using ORD function for multi-byte characters.
 
This query returns the decimal character code for 
the leftmost character of Chinese characters 汉字. 
*/
SELECT ORD('汉字');

Query result set - 1 row returned:
Using ORD function for multi-byte characters

Practice #28-2: Using ORD function for single-byte characters

/*
Using ORD function for single-byte characters. In this
case, it returns the same value as the ASCII() function.
 
Query 1: This query returns the decimal character code for 
the leftmost character of string GeeksEngine. 
*/
SELECT ORD('GeeksEngine');
 
/*
Query 2: This query returns the same value as Query 1.
*/
SELECT ASCII('GeeksEngine');

Query result set by ORD() function - 1 row returned:
Using ORD function for single-byte characters

Query result set by ASCII() function - 1 row returned:
Using ASCII function for single-byte characters

Practice #30: Using QUOTE() function to maintain escape character backslash (\) and single quotes

/*
Query 1: This query displays the backslash
and single quotes in the result. Here backslash 
does not work as escape character anymore. 
 
Compare with Query 2 to see the difference.
*/
SELECT QUOTE('You\'re cool!') AS quote_me;
 
/*
Query 2: In this query, single quotes and backslash
are not displayed in the result.
*/
SELECT 'You\'re cool!' AS no_quote;

Query result set by using QUOTE() function - 1 row returned:
$Using QUOTE() function to maintain escape character backslash (\) and single quotes$

Query result set without using QUOTE() function - 1 row returned:
No QUOTE function is used

Next tutorial: Using String Functions, Part 3
Back to Section Index: Single Row Functions
Back to Tutorial Index: MySQL Database How-to Articles and Tutorials

	Custom Search