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:
- Part 1 of MySQL String Functions
- Part 3 of MySQL String Functions
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
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT INSTR('MSExcelOfficeExcel', 'Excel');
Query result set - 1 row returned:
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
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT LCASE('GeeksEngine');
SELECT LOWER('GeeksEngine');
Query result set - 1 row returned:
Practice #18: Using LEFT string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT LEFT('GeeksEngine', 5);
Query result set - 1 row returned:
Practice #19-1: Using LENGTH() string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT LENGTH('GeeksEngine');
Query result set - 1 row returned:
Practice #19-2: Using LENGTH() string function for multi-byte characters
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
Query result set - 1 row returned:
Practice #20: Using LOAD_FILE() string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT LOAD_FILE('/Program Files/MySQL/MySQL Server 5.0/my.ini');
Query result set - 1 row returned in Text view:
Practice #21-1: Using LOCATE string function with position number omitted
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT LOCATE('engine', 'GeeksEngine');
Query result set - 1 row returned:
Practice #21-2: Using LOCATE string function with position number specified
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT LOCATE('engine', 'GeeksEngineEngine', 8);
Query result set - 1 row returned:
Practice #23-1: Using LPAD() function with same len and pos value
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT LPAD('Engine',11,'Geeks');
Query result set - 1 row returned:
Practice #23-2: Using LPAD() function with shorter len value
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT LPAD('Engine',4,'Geeks');
Query result set - 1 row returned:
Practice #23-3: Using LPAD() function with longer len value
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT LPAD('Engine',15,'Geeks');
Query result set - 1 row returned:
Practice #24: Using LTRIM function to remove leading spaces
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
Query result set - 1 row returned:
Practice #25: Using MAKE_SET string function
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT MAKE_SET(1 | 4,'hello','nice','world');
Query result set - 1 row returned:
Practice #28-1: Using ORD function for multi-byte characters
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
Query result set - 1 row returned:
Practice #28-2: Using ORD function for single-byte characters
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT ORD('GeeksEngine');
SELECT ASCII('GeeksEngine');
Query result set by ORD() function - 1 row returned:
Query result set by ASCII() function - 1 row returned:
Practice #30: Using QUOTE() function to maintain escape character backslash (\) and single quotes
Most of the queries in the tutorials need Northwind MySQL database, you can download the database script on this page.
SELECT QUOTE('You\'re cool!') AS quote_me;
SELECT 'You\'re cool!' AS no_quote;
Query result set by using QUOTE() function - 1 row returned:
Query result set without using QUOTE() function - 1 row returned:
Other tutorials in this category
1. Using String Functions, Part 1
2. Using String Functions, Part 3
3. Using MySQL Date and Time Functions, Part 1
4. Using MySQL Date and Time Functions, Part 2
5. Using MySQL Date and Time Functions, Part 3
6. Using MySQL Control Flow Functions
7. Using MySQL Conversion Functions
8. Using MySQL Comparison Functions
9. Using Aggregate Functions in MySQL
10. MySQL RANK() function
11. MySQL DENSE_RANK() function
12. MySQL ROW_NUMBER() function
13. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function
14. Using LIMIT and OFFSET to Page Thru Query Result in MySQL
15. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL
16. Use LEAST function to find the SMALLEST value from multiple arguments
17. How to make case-sensitive comparison in MySQL
18. Use GREATEST function to find the LARGEST value from multiple arguments
19. MySQL MID() function with examples
20. MySQL LOCATE() function with examples
21. MySQL SUBSTR() function with examples
22. MySQL POSITION() function with examples
23. How to use MySQL REPLACE function in SELECT and UPDATE queries
Back to Tutorial Index Page