| The rest of this chapter lists the string functions in alphabetical order.
AES_DECRYPT(string, password) | |
This function decrypts text that was encrypted using the AES algorithm with a 128-bit key length, and it returns NULL if one of the given parameters is NULL. This function reverses the AES_ENCRYPT() function. This function is available as of Version 4.0.2 of MySQL. SELECT AES_DECRYPT(personal, 'my_password') AS Personal FROM teachers WHERE teacher_id='730522'; +----------+ | Personal | +----------+ | text | +----------+
In this example, the value for the personal column is decrypted using the password given. The result is just the plain text.
AES_ENCRYPT(string, password) | |
This function returns encrypted text using the AES algorithm with a 128-bit key length and returns NULL if one of the given parameters is NULL. The results of this function can be reversed with AES_DECRYPT( ). This function is available as of Version 4.0.2 of MySQL. UPDATE teachers SET personal = AES_ENCRYPT('text', 'my_password') WHERE teacher_id = '730522';
This function returns the ASCII code for the first character of a given string. If you're entering raw text, you must enclose string within quotes. If the argument is a column, do not enclose it in quotes. If string is empty, 0 is returned. SELECT ASCII('A') AS A, ASCII(name_last) AS Professor FROM teachers WHERE name_last = 'McAllister'; +----+-----------+ | A | Professor | +----+-----------+ | 65 | 77 | +----+-----------+
Only the M in the professor's name is converted to its ASCII equivalent. It displays the ASCII value for A and the first letter of the name.
This function returns a binary number for a given integer. It returns NULL if the string is NULL. SELECT BIN(1), BIN(2), BIN(3); +--------+--------+--------+ | BIN(1) | BIN(2) | BIN(3) | +--------+--------+--------+ | 1 | 10 | 11 | +--------+--------+--------+
For the number 1 in a base 10 system, the first position in a binary system is on or 1. For the number 2, the first position from the right is off and the second is on. For 3, the first and the second positions are on.
Use this function to treat strings in their binary state. This function is useful for making SQL statements case-sensitive. Notice that the syntax does not call for parentheses: SELECT student_id, name_last FROM students WHERE BINARY LEFT(UCASE(name_last), 1) <> LEFT(name_last, 1); +------------+-----------+ | student_id | name_last | +------------+-----------+ | 433302000 | dyer | | 434016005 | de Vitto | +------------+-----------+
This SQL statement checks for any students whose last name starts with a lowercase letter. To do this, each student's last name is converted to uppercase letters and then the first letter starting from the left is extracted to compare it to the first letter of the last name without case conversion. The results show one record that is probably a typing error and another (the second one) that is probably correct. Notice that the BINARY keyword is specified before the comparison is made between the strings and is applied to both strings.
This function returns the number of bits in a given string. The following example uses the default character set, where one character requires 8 bits. SELECT BIT_LENGTH('a') AS 'One Character', BIT_LENGTH('ab') AS 'Two Characters'; +---------------+----------------+ | One Character | Two Characters | +---------------+----------------+ | 8 | 16 | +---------------+----------------+
This function returns a string or text for ASCII code. This is the reverse of ASCII( ). This statement returns NULL values. SELECT ASCII('A'), CHAR('65'); +------------+------------+ | ASCII('A') | CHAR('65') | +------------+------------+ | 65 | A | +------------+------------+
This function returns the number of characters of a given string. A multiple-byte character is treated as one character. This is synonymous with CHARACTER_LENGTH(). SELECT course_id, CASE WHEN CHAR_LENGTH(course_desc) > 30 THEN CONCAT(SUBSTRING(course_desc, 1, 27), '...') ELSE course_desc END AS Description FROM courses;
In this example, a CASE control statement is used to specify different display results based on a condition. Using the CHAR_LENGTH( ) function MySQL determines if the content of course_desc is longer than 30 characters. If it is, the SUBSTRING( ) function extracts the first 27 characters and the CONCAT( ) function adds ellipsis points to the end of the truncated data to indicate that there is more text. Otherwise, the full contents of course_desc are displayed.
This function returns the number of characters of a given string. A multiple-byte character is treated as one character. This is synonymous with CHAR_LENGTH( ).
This function returns a given string compressed. It requires MySQL to have been compiled with a compression library (i.e., zlib). This statement is available as of Version 4.1 of MySQL. SELECT COMPRESS(essay) FROM applications WHERE applicant_id = '7382';
With this function, strings of text and strings from columns can be concatenated or pasted together under one resulting column. If any of the strings are NULL, a NULL value is returned for the selected row. SELECT CONCAT(name_first, ' ', name_last) AS Student FROM students WHERE name_last = 'Dyer'; +-------------------+ | Student | +-------------------+ | Kenneth Dyer | | Geoffrey Dyer | | Marie Dyer | | NULL | +-------------------+
In this example, the database contained four students with the last name Dyer, but for one of them there was a NULL value in the name_first column. Within the parentheses of the function, notice that a space is given within quotes as the second element so that the display shows a space between each student's first and last name.
CONCAT_WS(separator, string, . . . ) | |
This function combines strings of text and columns together with a separator specified in the first argument. Any number of strings may be specified after the first argument, with each argument separated by a comma. Null values are ignored. SELECT CONCAT_WS('|', student_id, name_last, name_first) AS 'student_record' FROM students LIMIT 3; +-----------------------------+ | student_record | +-----------------------------+ | 433342000|Dyer|Russell | | 434892001|Dyer|Marie | | 433892002|Zabalaoui|Michael | +-----------------------------+
Here the vertical bar is used to separate the columns. This function can be useful for exporting data to formats acceptable to other software. However, you will require some programming to export the data to an external file using an SQL statement such as this one.
CONV(number, from_base, to_base) | |
This function converts a number from one numeric base system to another. The number to convert is given in the first argument, the base from which to convert the number in the second argument, and the base to which to convert the number in the third argument. The minimum base allowed is 2 and the maximum is 36. SELECT CONV(4, 10, 2) AS 'Base-10 4 Converted', CONV(100, 2, 10) AS 'Binary 100 Converted'; +---------------------+----------------------+ | Base-10 4 Converted | Binary 100 Converted | +---------------------+----------------------+ | 100 | 4 | +---------------------+----------------------+
Here the number 4 under the base 10 system is converted to the base-two or binary equivalent and back again.
DECODE(encrypted, password) | |
This function decrypts a given string that was encrypted with a given password. SELECT ENCODE(pwd, 'oreilly') FROM teachers WHERE teacher_id = '730522';
This function decrypts the contents of the pwd column and unlocks it using the oreilly password, which was used to encrypt it originally using ENCODE( ).
DES_DECRYPT(string, [key]) | |
This function decrypts text that was encrypted using the triple DES algorithm with a 128-bit key length. It returns NULL if an error occurs. This function will work only if MySQL has been configured for secure sockets layer (SSL) support. It reverses the DES_ENCRYPT( ) function. This function is available as of Version 4.0.1 of MySQL. SELECT DES_DECRYPT(credit_card_nbr, 0) FROM orders WHERE order_nbr = '8347';
In this example, the value for the credit_card_nbr column is decrypted using the first key string in the key file. See the description of DES_ENCRYPT( ) for more information on key files.
DES_ENCRYPT(string, [key]) | |
This function returns encrypted text using the triple DES algorithm with a 128-bit key length. It returns NULL if an error occurs. This function will work only if MySQL has been configured for SSL support. To use this function, a key file must be created and specified with the --des-key-file option when starting the mysqld daemon. To encrypt a given string, either a key string must be given as the second argument of the function, or it can be retrieved from a file containing key strings. A key file should be set up with a separate key string on each line. Each line should begin with a single-digit number (0-9) as an index, followed by a space before the key string (e.g., key_number des_string). To reference key strings in the key file, the number of the line to retrieve may be given as the key to the function. If a key number is not given with this function, the first key in the key file is used for encryption. The results of this function can be reversed with DES_DECRYPT( ). This function is available as of Version 4.0.1 of MySQL. UPDATE orders SET credit_card_nbr = DES_ENCRYPT('4011-7839-1234-4321') WHERE order_nbr = '8347';
ELT(number, string, . . . ) | |
This function returns the number element from the list of remaining arguments given. If the number given is less than one or if the number of elements is less than the number given, this statement will return NULL. SELECT ELT(2, 'one','two','three') AS 'ELT( ) Test'; +------------+ | ELT( ) Test | +------------+ | two | +------------+
This function encrypts a given string in binary format and locks it with the password. You should not use this function for the password column in the user table of the mysql database. Use PASSWORD( ) instead. UPDATE teachers SET pwd = ENCODE('test', 'oreilly') WHERE teacher_id = '730522';
The function here encrypts the word "test" and locks it with the oreilly password. The results are stored in the pwd column for the teacher chosen. To unlock the results, you can use the DECODE( ) function with the password.
This function returns encrypted text using the C function crypt. A two-character string may be given in the second argument to increase the randomness of encryption. Encryption cannot be decrypted in MySQL. You should not use this function for the password column in the user table of the mysql database. Use PASSWORD( ) instead. UPDATE teachers SET pwd = ENCRYPT('test', 'JT') WHERE teacher_id = '730522';
EXPORT_SET(number, on, off[, separator,[count]]) | |
This function returns a series of strings in order that represent each bit of a given number. The equivalent of 1 (an on bit) is given in the second argument and the equivalent of 0 (an off bit) is given in the third. A separator may be specified in the fourth argument, and a number of bit equivalents to display for the last argument. The default is a comma. SELECT BIN(4) AS 'Binary Number', EXPORT_SET(4, 'on', 'off', '-', 8) AS 'Verbal Equivalent'; +---------------+--------------------------------+ | Binary Number | Verbal Equivalent | +---------------+--------------------------------+ | 100 | off-off-on-off-off-off-off-off | +---------------+--------------------------------+
Notice that the conversion of the binary equivalent of 4 is displayed by EXPORT_SET() in what one might consider reverse order, from right to left: not 100, but 001 (or rather, 00100000) instead.
FIELD(string, string[, ...]) | |
This function searches for the first string given in the list of other strings and returns the numeric position of the first argument that matches. The first element is 1 among the arguments being searched. If the search string is not found or is NULL, 0 is returned. SELECT FIELD('test', 'test','one','two','three') AS 'FIELD( ) Test'; +--------------+ | FIELD( ) Test | +--------------+ | 1 | +--------------+
Notice that each string is a separate argument. To provide one string containing a comma-separated list instead, use FIND_IN_SET( ).
FIND_IN_SET(string, string_list) | |
This function returns the location of a string given in the first argument of the function, in a comma-separated list in a string given in the second argument. The first element of the list is 1. A 0 is returned if the string is not found in the set. It returns NULL if either argument is NULL. SELECT FIND_IN_SET('test', 'test,one,two,three') AS 'FIND_IN_SET( ) Test'; +---------------------+ | FIELD_IN_SET( ) Test | +---------------------+ | 1 | +---------------------+
This function returns the hexadecimal value of number. SELECT CONCAT('#', HEX(255), HEX(255), HEX(255)) AS White; +---------+ | White | +---------+ | #FFFFFF | +---------+
This SQL statement converts the RGB (red, green, and blue) values given to their hexadecimal equivalent for the color white.
This function converts an Internet Protocol (IP) address in dot-quad notation to its numeric equivalent. The function INET_NTOA( ) can be used to reverse the results. SELECT INET_ATON('12.127.17.72') AS 'AT&T'; +-----------+ | AT&T | +-----------+ | 209654088 | +-----------+
This function is useful in sorting IP addresses that lexically might not sort properly. For instance, an address of 10.0.11.1 would come after 10.0.1.1 and before 10.0.2.1 under normal sort conditions in an ORDER BY clause.
This function converts numeric equivalent of an IP address to an IP address equivalent. The function INET_ATON( ) can be used to reverse the results. SELECT INET_NTOA('209654088') AS 'AT&T'; +--------------+ | AT&T | +--------------+ | 12.127.17.72 | +--------------+
INSERT(string, position, length, new_string) | |
This function inserts a string into another string at the specified position, optionally overwriting the new_string provided for a specified length. It returns NULL if any of the arguments are NULL. The first position is 1. To prevent overwriting, give a length of 0 for the third argument, which specifies how many characters to overwrite before inserting. Don't confuse this function with the INSERT statement. UPDATE courses SET course_name = INSERT(course_name, LOCATE('Eng.', course_name), 4, 'English') WHERE course_name LIKE "%Eng.%";
In this example, it was discovered that some course names have the word "English" abbreviated as "Eng." This SQL statement overwrites any such occurrences with the word "English." It uses the LOCATE( ) function to find the starting point of the abbreviation. This number is used as the position argument for the INSERT( ) function. If it's not found, the course name will not be changed.
This function returns the starting position of the first occurrence of the substring. The index of the first position is 1. This function is case-insensitive unless the argument is a binary string. SELECT INSTR('Where are you?', 'you') AS 'INSTR( ) Test'; +--------------+ | INSTR( ) Test | +--------------+ | 11 | +--------------+
This function converts a string of text or a string from a column given to all lowercase letters. An alias to LOWER( ). SELECT teacher_id AS 'Teacher ID', CONCAT(LEFT(UCASE(name_last), 1), SUBSTRING(LCASE(name_last), 2)) AS Teacher FROM teachers;
To ensure that the first letter of the teacher's name is displayed in uppercase and the rest of the name is in lowercase letters, a combination of the LEFT( ) function and the SUBSTRING() function is used in conjunction with the UCASE( ) function and the LCASE( ) function.
This function returns characters from a string given in the first argument for the length specified in the second argument, starting from the left end of the string. SELECT LEFT(home_telephone, 3) AS 'Area Code' FROM students GROUP BY 'Area Code';
This SQL statement extracts the first three digits of the telephone number of each student, which is the telephone area code, and then groups the results by the Area Code alias to determine the telephone area codes in which students reside.
This function returns the number of bytes contained in a given string. Note that there are eight bits to a byte and there is one byte to a letter. OCTET_LENGTH() is an alias. SELECT LENGTH('test') AS 'Number of Bytes'; +-----------------+ | Number of Bytes | +-----------------+ | 4 | +-----------------+
There are four letters in the word "test," so the string examined is composed of four bytes.
This function reads the contents of a file. The user must have file privileges in MySQL, and the file must be readable by all users on the filesystem. It returns NULL if the file doesn't exist, if the user doesn't have proper permissions, or if the file is otherwise unreadable. UPDATE applications SET essay = LOAD_FILE('/tmp/smith_john.txt') WHERE applicant_id = '7382';
In this example, an essay written by someone who is applying for admission to the university is loaded into the essay column (which is a BLOB datatype) of the row for the applicant in the applications table. The entire contents of the file, including any binary data such as hard returns and photographs, are loaded from the file into the table.
LOCATE(string, string[, start_position]) | |
This function returns the numeric starting point of the first occurrence of a string given in the first argument, of the function found in the string given in the second argument. MySQL does not search beyond this point. A starting position for searching may be specified as a third argument. SELECT name_last AS 'Name', LOCATE('n', name_last) AS 'First n', LOCATE('n', name_last, 5) AS 'After 5th Char' FROM teachers WHERE teacher_id = '730522'; +--------+---------+----------------+ | Name | First n | After 5th Char | +--------+---------+----------------+ | Vernon | 4 | 6 | +--------+---------+----------------+
In this SQL statement, the last name of the teacher selected contains the letter "n" twice. However, the first use of the LOCATE( ) function makes note only of the position of the first occurrence. The second use of LOCATE( ) gives a starting point for MySQL to search name_last that is beyond the first occurrence, so the position of the next one is returned.
This function converts a string of text or a column given to all lowercase letters. It is synonymous with LCASE( ). SELECT course_id AS 'Course ID', LOWER(course_name) AS Course FROM courses;
This statement displays the name of each course in all lowercase letters.
LPAD(string, length, padding) | |
This function adds padding specified in the third argument of the function to the left end of the string given, until the result reaches the maximum length (second argument) of the string contents and padding combined. SELECT LPAD(course_name, 25, '.') AS Courses FROM courses LIMIT 3; +---------------------------+ | Courses | +---------------------------+ | .........Creative Writing | | .....Professional Writing | | ......American Literature | +---------------------------+
In this example, a list of three courses is retrieved and the results are padded with dots to the left of the course names.
The function returns a string given with any leading spaces removed. UPDATE students SET name_last = LTRIM(name_last);
In this example, the last names of several students have been entered inadvertently with a space in front of the names. This SQL statement removes any leading spaces from each name retrieved and then writes the trimmed text over the existing data.
MAKE_SET(bits, string1, string2, ...) | |
This function returns a comma-separated list of values that coincide with bits of a base 10 number converted to its binary equivalent. SELECT BIN(9) AS 'Binary 9', MAKE_SET(100, 'A','B','C','D') AS Set; +----------+------+ | Binary 9 | Set | +----------+------+ | 1001 | A,D | +----------+------+
Notice that the binary equivalent of 9 is 1001. The first bit starting from the right of the binary number shown is 1 (or on), so the first string starting from the right of the list of strings is put into the set. The second and third bits of the binary number are 0, so the second and third strings (i.e., B and C) are not added to the set. The fourth bit counting from the right is 1, so the fourth string of the list of strings is added to the set.
MATCH(column[, ...]) AGAINST (string) | |
Use this function in WHERE clauses as a condition to search table columns for a given string. Text in the string delimited by spaces or quotes is parsed into separate words. Small words (three characters or less) are ignored. Then the words are used for searching columns. It requires a FULLTEXT index for columns searched. SELECT applicant_id FROM applications WHERE MATCH (essay) AGAINST ('English');
This SQL statement searches the table containing data on people applying for admission to the university. The essay column contains a copy of the applicant's admission essay. The column is searched for applicants who mention the word "English" so that a list of applicants that have voiced an interest in the English program will be displayed.
This function returns 32-character hash value of string from the RFC 1321 standard. SELECT MD5('Test') AS 'MD5( ) Test'; +----------------------------------+ | MD5( ) Test | +----------------------------------+ | 0cbc6611f5540bd0809a388dc95a615b | +----------------------------------+
MID(string, position[, length]) | |
This function returns the characters of a given string, starting from the left position specified in the second argument. The first character is numbered 1. You can limit the length of the string retrieved by specifying a limit in the third argument. This function is similar to SUBSTRING( ). SELECT CONCAT(name_first, ' ', name_last) AS Teacher, CONCAT('(', LEFT(home_telephone, 3), ') ', MID(home_telephone, 4, 3), '-', MID(home_telephone, 7)) AS Telephone FROM teachers LIMIT 1; +----------------+----------------+ | Teacher | Telephone | +----------------+----------------+ | Olympia Vernon | (504) 230-1748 | +----------------+----------------+
This convoluted SQL statement produces the output shown by concatenating the extracted components of the selected teacher's telephone number with opening and closing parentheses, a dash, and a space.
This function returns the octal or base 8 numeric system value of the given number. It returns NULL if the argument is NULL. SELECT OCT(1), OCT(9), OCT(16); +--------+--------+---------+ | OCT(8) | OCT(9) | OCT(16) | +--------+--------+---------+ | 10 | 11 | 20 | +--------+--------+---------+
This function returns the number of bytes contained in the given string. Note that there are eight bits to a byte and there is one byte to a letter. An octet is a term used to describe the components of a IP address. This function is an alias of LENGTH( ).
This function encrypts a given string based on the password encryption method used prior to Version 4.1 of MySQL. The result is not decryptable. UPDATE teachers SET pwd = OLD_PASSWORD('test') WHERE teacher_id = '730522';
This function returns an ordinal value, the position of a character in the ASCII character set, of the first character from the left in a string given. SELECT ORD('A'), ORD('a'); +----------+----------+ | ORD('A') | ORD('a') | +----------+----------+ | 65 | 97 | +----------+----------+
This function encrypts a password given as an argument. The result is not decryptable. This function is used for encrypting data in the password column of the user table in the mysql database. UPDATE teachers SET pwd = PASSWORD('test') WHERE teacher_id = '730522';
POSITION(substring IN string) | |
This function returns the numeric starting point from the left of the first occurrence of the string given in the first argument of the function found in the string given in the second. MySQL does not search beyond this point. This function is similar to LOCATE( ). SELECT name_last AS 'Name', POSITION('n' IN name_last) AS 'First n' FROM teachers WHERE teacher_id = '730522'; +--------+---------+ | Name | First n | +--------+---------+ | Vernon | 4 | +--------+---------+
In this SQL statement, the last name of the teacher selected contains the letter "n" twice, but only the position of the first occurrence is returned.
This function returns a value as an input-safe string for a given string enclosed in single quotes. Single quotes, backslashes, ASCII NULLs, and Ctrl-Zs contained in the string are escaped with a backslash. This is a useful security measure when accepting values from a public web interface. SELECT QUOTE(course_name) AS Courses FROM courses WHERE course_name LIKE "%'%" LIMIT 1; +-------------------+ | Courses | +-------------------+ | Works of O\'Henry | +-------------------+
In the WHERE clause of this SQL statement, the LIKE operator is used to find courses with a single quote in their names. The % is a wildcard. Because of the QUOTE() function, the single quote in the field returned is escaped with a backslash.
This function returns the string given in the first argument of the function, as many times as specified in the second argument. It returns an empty string if count is less than or equal to 0. It returns NULL if either argument is NULL. SELECT REPEAT('Urgent! ', 3) AS 'Warning Message';
REPLACE(string, old_element, new_element) | |
This function replaces occurrences of an element given in the second argument, that exists in the string given in first argument, with a new element given in the third. SELECT student_id, CONCAT(REPLACE(title, 'Mrs.', 'Ms.'), ' ', name_first, ' ', name_last) AS Name FROM students;
This SQL statement will retrieve each student's title and replace any occurrences of "Mrs." with "Ms.," but will leave all other titles unchanged.
This function returns characters of string given in reverse order. SELECT REVERSE('MUD'); +----------------+ | REVERSE('MUD') | +----------------+ | DUM | +----------------+
This function returns characters from string given in the first argument for the length specified in the second argument, starting from the right end of the string. SELECT RIGHT(soc_sec, 4) FROM students WHERE student_id = '43325146122';
This SQL statement retrieves the last four digits of the student's Social Security number as an identity verification.
RPAD(string, length, padding) | |
This function adds padding specified in the third argument of the function to left end of the string given in the first argument, until the maximum length (second argument) of string and padding combined is reached. SELECT RPAD(course_name, 25, '.') AS Courses FROM courses LIMIT 3; +---------------------------+ | Courses | +---------------------------+ | Creative Writing......... | | Professional Writing..... | | American Literature...... | +---------------------------+
This SQL statement presents a list of three course names that are retrieved. The results are padded with dots to the right of each.
This function returns the string given with any trailing spaces removed. UPDATE students SET name_last = RTRIM(name_last);
In this example, the last names of several students have been entered inadvertently with a space at the end of the names. This SQL statement will remove any trailing spaces from each name retrieved and then write the trimmed text over the existing data.
This function returns the Secure Hash Algorithm (SHA) 160-bit checksum for the given string. The results are a string composed of 40 hexadecimal digits. NULL is returned if the given string is NULL. This function is synonymous with SHA1( ). SELECT SHA('test'); +------------------------------------------+ | SHA('test') | +------------------------------------------+ | a94a8fe5ccb19ba61c4c0873d391e987982fbbd3 | +------------------------------------------+
This function returns the SHA 160-bit checksum for the given string. The results are a string composed of 40 hexadecimal digits. NULL is returned if the given string is NULL. This function is synonymous with SHA( ).
This function returns an alphanumeric equivalent of a string based on an algorithm. SELECT IF(SOUNDEX('him') = SOUNDEX('hymm'), 'Sounds Alike', 'Does not sound alike') AS 'Sound Comparison'; +------------------+ | Sound Comparison | +------------------+ | Sounds Alike | +------------------+
SOUNDEX( ) was designed to allow comparisons between fuzzy input, but it's rarely used.
This function returns a string of spaces. The number of spaces returned is set by the argument. SELECT CONCAT(name_first, SPACE(1), name_last) AS Name FROM students LIMIT 1; +------------------+ | Name | +------------------+ | Richard Stringer | +------------------+
Although this requires more typing than just placing a space within quotes, it's more visible to a human reader. Also, you could substitute the count with a variable in a program, and adjust it for the length of the name or some other factor.
This function compares two strings to determine whether the first string is before or after the second string in ASCII sequence. If the first string precedes the second, -1 is returned. If it follows the second, 1 is returned. If they are equal, 0 is returned. This function is often used for alphanumeric comparisons, but is case-sensitive. SELECT STRCMP('test','text') AS Comparison; +------------+ | Comparison | +------------+ | -1 | +------------+
SUBSTRING(string, position[, length]) SUBSTRING(string FROM position FOR length) | |
This function returns the characters of a given string, starting from the left position given. The first character is numbered 1. You can restrict the length of the string retrieved by specifying a limit. This function is similar to MID( ). SELECT CONCAT(SUBSTRING(soc_sec, 1, 3), '-', SUBSTRING(soc_sec FROM 4 FOR 2), '-', SUBSTRING(soc_sec FROM 6)) AS 'Social Security Nbr.' FROM students LIMIT 1; +----------------------+ | Social Security Nbr. | +----------------------+ | 433-12-3456 | +----------------------+
This example shows the three types of syntax of SUBSTRING() for reformatting a Social Security number stored without dashes.
SUBSTRING_INDEX(string, delimiter, count) | |
This function returns a substring of the string given in the first argument that contains delimited data. The delimiter needs to be given in the second argument of the function. The number of elements starting from the left end of the string to extract is given in the third argument. A negative number instructs MySQL to count from the right end. SELECT SUBSTRING_INDEX(pre_req, '|', -1) AS 'Last Prerequisite', pre_req AS 'All Prerequisites' FROM courses WHERE course_id = '1245'; +--------------------+----------------------------+ | Last Prerequisite | All Prerequisites | +--------------------+----------------------------+ | ENGL-202 | ENGL-101|ENGL-201|ENGL-202 | +--------------------+----------------------------+
In this example, the last prerequisite course for the course selected is displayed, because -1 was entered for the count.
TRIM([[BOTH | LEADING | TRAILING] [padding] FROM] string) | |
This function returns the string given with any trailing or leading padding removed, depending on which are specified. The default padding is a space if none is specified. SELECT TRIM(LEADING '.' FROM col1), TRIM(TRAILING FROM col2), TRIM(BOTH '-' FROM col3), TRIM(col4) FROM table1;
In this generic example, leading dots will be removed from the output of col1, spaces will be eliminated from col2, leading and trailing hyphens will be removed from col3, and leading and trailing spaces will be removed from col4.
This function converts the given string of text or a string from a column to all uppercase letters. It is synonymous with UPPER() function. SELECT course_id AS 'Course ID', UCASE(course_name) AS Course FROM courses LIMIT 3; +-----------+----------------------+ | Course ID | Course | +-----------+----------------------+ | 1245 | CREATIVE WRITING | | 1255 | PROFESSIONAL WRITING | | 1244 | AMERICAN LITERATURE | +-----------+----------------------+
This function returns uncompressed string. It reverses the results of the COMPRESS( ) function. It requires MySQL to have been compiled with a compression library (i.e., zlib), and it returns NULL if the string is not compressed or if MySQL wasn't compiled with zlib. SELECT UNCOMPRESS(essay) FROM applications_archive WHERE applicant_id = '1748';
UNCOMPRESSED_LENGTH(string) | |
This function returns the number of characters contained in the given string after decompression. You can compress strings using the COMPRESS( ) function. This function is available as of Version 4.1 of MySQL. SELECT UNCOMPRESSED_LENGTH(essay) FROM applications WHERE applicant_id = '1748';
This function converts hexadecimal numbers to their character equivalent. It reverses the results of the HEX( ) function. This function is available as of Version 4.1.2 of MySQL. SELECT ORD(UNHEX(MID('#FFFFFF', 2,2))) AS 'Red Value', ORD(UNHEX(MID('#FFFFFF', 4,2))) AS 'Green Value', ORD(UNHEX(RIGHT('#FFFFFF', 2))) AS 'Blue Value'; +-----------+-------------+------------+ | Red Value | Green Value | Blue Value | +-----------+-------------+------------+ | 255 | 255 | 255 | +-----------+-------------+------------+
This SQL statement returns the RGB values for the color white. The ORD( ) function is used to convert the results of UNHEX( ) to plain text.
This function converts the given string of text or a string from a column to all uppercase letters. It is synonymous with the UCASE() function. | 