Manipulating Text in MySQL

Revision as of 18:02, 11 May 2016 by Neil (Talk | contribs) (Text replacement - "<htmlet>ezoicbottom</htmlet>" to "")

Revision as of 18:02, 11 May 2016 by Neil (Talk | contribs) (Text replacement - "<htmlet>ezoicbottom</htmlet>" to "")

PreviousTable of ContentsNext
MySQL Calculations and ConcatenationsMySQL Mathematical Functions


Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99


MySQL provides a number of built-in functions that can be used to both manipulate text values, and also to perform mathematical calculations on numerical values. The purpose of this chapter is to provide an overview of these functions.

MySQL String Manipulation Functions

MySQL provides wide selection of functions which may be called during a SQL statement to make changes to text values. The following table lists the most frequently used functions in this category. Some example of how to use these functions are included after the table. For extensive details on how to use each of these functions refer to the MySQL Reference Guide:

Name Description
ASCII() Returns numeric value of left-most character
BIN() Returns a string representation of the argument
BIT_LENGTH() Returns length of argument in bits
CHAR_LENGTH() Returns number of characters in argument
CHAR() Returns the character for each integer passed
CHARACTER_LENGTH() The same as CHAR_LENGTH()
CONCAT_WS() Returns concatenate with separator
CONCAT() Returns concatenated string
CONV() Converts numbers between different number bases
ELT() Returns string at index number
<=> NULL-safe equal to
= Equal
EXPORT_SET() Returns a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
FIELD() Returns the index (position) of the first argument in the subsequent arguments
FIND_IN_SET() Returns the index position of the first argument within the second argument
FORMAT() Returns a number formatted to specified number of decimal places
>= Greater than or equal
> Greater than
HEX() Returns a string representation of a hex value
INSERT() Insert a substring at the specified position up to the specified number of characters
INSTR() Returns the index of the first occurrence of substring
IS NULL NULL value test
IS Test a value against a boolean
LCASE() The same as LOWER()
LEFT() Returns the leftmost number of characters as specified
LENGTH() Returns the length of a string in bytes
<= Less than or equal
< Less than
LIKE Simple pattern matching
LOAD_FILE() Load the named file
LOCATE() Returns the position of the first occurrence of substring
LOWER() Returns the argument in lowercase
LPAD() Returns the string argument, left-padded with the specified string
LTRIM() Removes leading spaces
MAKE_SET() Returns a set of comma-separated strings that have the corresponding bit in bits set
MID() Returns a substring starting from the specified position

!=, <>

Not equal
NOT LIKE Negation of simple pattern matching
NOT REGEXP Negation of REGEXP
OCT() Returns a string representation of the octal argument
OCTET_LENGTH() The same as LENGTH()
ORD() If the leftmost character of the argument is a multi-byte character, returns the code for that character
POSITION() The same as LOCATE()
QUOTE() Escape the argument for use in an SQL statement
REGEXP Pattern matching using regular expressions
REPEAT() Repeat a string the specified number of times
REPLACE() Replace occurrences of a specified string
REVERSE() Reverse the characters in a string
RIGHT() Returns the specified rightmost number of characters
RLIKE The same as REGEXP
RPAD() Append string the specified number of times
RTRIM() Removes trailing spaces
SOUNDEX() Returns a soundex string (an alphanumeric string representing the phonetic representation of the word. Useful for finding words that sound similar)
SOUNDS LIKE() Compares sounds
SPACE() Returns a string of the specified number of spaces
STRCMP() Compares two strings
SUBSTRING_INDEX() Returns a substring from a string before the specified number of occurrences of the delimiter

SUBSTRING(), SUBSTR()

Returns the substring as specified
TRIM() Removes leading and trailing spaces
UCASE() The same as UPPER()
UNHEX()() Converts each pair of hexadecimal digits to a character
UPPER() Converts to uppercase

For example, we might want to convert the text returned by a SELECT statement to upper case:

mysql> select UPPER(prod_name) from product where prod_code=4;
+--------------------------+
| UPPER(prod_name)         |
+--------------------------+
| MICROSOFT 10-20 KEYBOARD |
+--------------------------+
1 row in set (0.00 sec)

Alternatively, we might be interested to know the length of a column value:

mysql> SELECT prod_name, LENGTH(prod_name) FROM product where prod_code=4;
+--------------------------+-------------------+
| prod_name                | LENGTH(prod_name) |
+--------------------------+-------------------+
| Microsoft 10-20 Keyboard |                24 |
+--------------------------+-------------------+
1 row in set (0.03 sec)

We could also replace one word with another:

mysql> SELECT  REPLACE(prod_name, 'Microsoft', 'Apple') FROM product where prod_code=4;
+------------------------------------------+
| REPLACE(prod_name, 'Microsoft', 'Apple') |
+------------------------------------------+
| Apple 10-20 Keyboard                     |
+------------------------------------------+
1 row in set (0.00 sec)

Purchase and download the full PDF and ePub versions of this MySQL eBook for only $9.99



PreviousTable of ContentsNext
MySQL Calculations and ConcatenationsMySQL Mathematical Functions