Yo u can refer back to this lesso n later as a reference. We will co ver these functio ns: Charact e r Manipulat ing Funct io ns
ASCII(str) OCT(x)
CONV(x,y,z) HEX(x)
BIN(x) CHAR(x,y,z,...)
St ring Inf o Funct io ns
LENGTH(str) CHAR_LENGTH(str)
OCTET_LENGTH(str) CHARACTER_LENGTH(str) LOCATE(str1,str2) INSTR(str1,str2)
POSITION(str1 IN str2) SOUNDEX(str) LOCATE(str1,str2,x)
St ring Manipulat ing Funct io ns
CONCAT(str1,str2,...) SUBSTRING(str,x) LPAD(str1,x,str2) SUBSTRING(str FROM x) RPAD(str1,x,str2) SUBSTRING(str,x,y)
LEFT(str,x) SUBSTRING(str FROM x FOR y)
RIGHT(str,x) MID(str,x,y) LTRIM(str) SUBSTRING_INDEX(str1,str2,x) RTRIM(str) LCASE(str) LOAD_FILE(filen) LOWER(str) SPACE(str) UCASE(str) REPLACE(str1,str2,str3) UPPER(str) REPEAT(str,x) MAKE_SET(bit,str) REVERSE(str) EXPORT_SET(bit,str1,str2,[str3,[x]]) INSERT(str1,x,y,str2) FIND_IN_SET(str1,strlist) ELT(x,str1,str2,str3,...) FIELD(str1,str2,str3,str4,...) TRIM([[BOTH | LEADING | TRAILING] [str2] FROM] str1)
f ile n represents a filename including path to the file, st r represents string, st rlist represents a list string, bit represents bits, and x, y, and z represent numbers.
We'll explain each o f these in detail, with examples o f usage. We'll also explain the parameters and o utput.
ASCII(str)
INTERACTIVE SESSION:
mysql> select ascii('2'), ascii(2), ascii(20), ascii('blah'); +---+---+---+---+
| ascii('2') | ascii(2) | ascii(20) | ascii('blah') | +---+---+---+---+ | 50 | 50 | 50 | 98 | +---+---+---+---+
CONV(x,y,z)
This functio n co nverts numbers between different bases, such as hexadecimal to decimal, and binary to o ctal. x is the number being co nverted fro m base y to base z. The bases y and z can range fro m 2 to 36 . If the base has a preceding negative sign, x is treated as an unsigned number. x can also be a string. co nv() wo rks with 6 4-bit precisio n.
INTERACTIVE SESSION:
mysql> select conv("a",16,2), conv("6E",18,8); +---+---+
| conv("a",16,2) | conv("6E",18,8) | +---+---+ | 1010 | 172 | +---+---+
mysql> select conv(-17,10,-18), conv(10+"10"+'10'+0xa,10,10); +---+---+ | conv(-17,10,-18) | conv(10+"10"+'10'+0xa,10,10) | +---+---+ | -H | 40 | +---+---
BIN(x)
Returns the binary o f x, where x is in base 10 . This is identical to co nv(x,10 ,2). INTERACTIVE SESSION:
mysql> select bin(184), conv(184,10,2); +---+---+ | bin(184) | conv(184,10,2) | +---+---+ | 10111000 | 10111000 | +---+---+
OCT (x)
Returns the o ctal value o f x, where x is a decimal number. This is identical to co nv(x,10 ,8 ). INTERACTIVE SESSION:
mysql> select oct(43), conv(43,10,8); +---+---+
| oct(43) | conv(43,10,8) | +---+---+ | 53 | 53 | +---+---+
HEX(x)
Returns the hexadecimal value o f x, where x is a decimal number. This is identical to co nv(x,10 ,16 ). INTERACTIVE SESSION:
mysql> select hex(123), conv(123,10,16); +---+---+ | hex(123) | conv(123,10,16) | +---+---+ | 7B | 7B | +---+---+
CHAR(x,y,z,...)
Co nverts x, y, z, etc., to ASCII characters. x, y, z, etc. are decimal ASCII co des. If any o f the parameters are flo at values, they are truncated to integers.
INTERACTIVE SESSION:
mysql> select char(72, 79, 89.3, '79.9', 85, 76); +---+
| char(72, 79, 89.3, '79.9', 85, 76) | +---+ | HOYOUL | +---+
LENGT H(str), CHAR_LENGT H(str), OCT ET _LENGT H(str),
CHARACT ER_LENGT H(str)
These functio ns are all identical. They return the length o f the string st r. INTERACTIVE SESSION:
mysql> select length('this'), char_length('is'); +---+---+
| length('this') | char_length('is') | +---+---+ | 4 | 2 | +---+---+
mysql> octet_length('really'), character_length('cool'); +---+---+ | octet_length('really') | character_length('cool') | +---+---+ | 6 | 4 | +---+---+
LOCAT E(str1,str2), POSIT ION(str1 IN str2)
INTERACTIVE SESSION:
mysql> select locate('hak','what'), position('ssy' in 'missy'); +---+---+
| locate('hak','what') | position('ssy' in 'missy') | +---+---+ | 0 | 3 | +---+---+
LOCAT E(str1,str2,x)
Returns the po sitio n o f the first o ccurrence o f substring st r1 in the string st r2, starting at po sitio n x. If st r2 is no t fo und, returns a zero .
INTERACTIVE SESSION:
mysql> select locate('ufo','didyoufocus?',2); +---+
| locate('ufo','didyoufocus?',2) | +---+ | 6 | +---+
mysql> select locate('ufo','didyoufocus?',9); +---+ | locate('ufo','didyoufucus?',9) | +---+ | 0 | +---+
INST R(str1, str2)
Returns the po sitio n o f the first o ccurrence o f substring st r2 in the string st r1. This is identical to lo cat e and po sit io n, except that the substring is the seco nd argument in instr.
INTERACTIVE SESSION:
mysql> select instr('missy','ssy'); +---+ | instr('missy','ssy') | +---+ | 3 | +---+
SOUNDEX(str)
This is an interesting functio n. Returns the so undex o f the string st r. Similar-so unding strings sho uld have the same so undex value. Treats no n-alphabet strings as vo wels.
INTERACTIVE SESSION:
mysql> select soundex('greetings'), soundex('meeting'), soundex('mayor'); +---+---+---+
| soundex('greetings') | soundex('meeting') | soundex('mayor') | +---+---+---+ | G6352 | M352 | M600 | +---+---+---+
CONCAT (str1,str2,...)
Returns a string co mpo sed o f st r1, st r2, etc. If the parameters are integers, flo at o r o ther no n-string values, they are co nverted to a string.
INTERACTIVE SESSION:
mysql> select concat('po', 'ke', 'm', 'on'); +---+
| concat('po', 'ke', 'm', 'on') | +---+ | pokemon | +---+
LPAD(str1,x,str2)
The string st r2 is added to the left side o f string st r1 until the length o f the string is x. INTERACTIVE SESSION:
mysql> select lpad('h no!', 8, 'oo'); +---+
| lpad('h no!', 8, 'oo') | +---+ | oooh no! | +---+
RPAD(str1,x,str2)
The string st r2 is added to the right side o f string st r1 until the length o f the string is x. INTERACTIVE SESSION:
mysql> select rpad('a ghost said b', 18, 'oo'); +---+
| rpad('a ghost said b', 18, 'oo') | +---+ | a ghost said boooo | +---+
LEFT (str,x)
Returns part o f the st r string o f length x, starting fro m the beginning o f the string. INTERACTIVE SESSION:
mysql> select left('jimmythecricket', 5); +---+ | left('jimmythecricket', 5) | +---+ | jimmy | +---+
RIGHT (str,x)
INTERACTIVE SESSION:
mysql> select right('jimmythecricket', 7); +---+ | right('jimmythecricket', 7) | +---+0 | cricket | +---+
LT RIM(str)
Trims the space characters o ff o f the string st r fro m the left side o f the string and returns it. INTERACTIVE SESSION:
mysql> select ltrim(' whoopy'); +---+ | ltrim(' whoopy') | +---+ | whoopy | +---+
RT RIM(str)
Trims the space characters o ff the right side o f the string st r and returns it. INTERACTIVE SESSION:
mysql> select rtrim('snoopy '); +---+ | rtrim('snoopy ') | +---+ | snoopy | +---+
LOAD_FILE(filen)
Reads the file indicated by the string f ile n (with full path) and returns the co ntents o f the file as a string. No te that mo st servers—including OST's—do no t grant MySQL users file access thro ugh MySQL, so this functio n usually wo n't wo rk.
INTERACTIVE SESSION:
mysql> update table_name set some_column = load_file("/tmp/blah");
SPACE(x)
INTERACTIVE SESSION:
mysql> select concat('front', space(7), 'end'); +---+
| concat('front', space(7), 'end') | +---+ | front end | +---+
REPLACE(str1,str2,str3)
Replaces all o ccurrences o f st r2 to st r3 in string st r1. INTERACTIVE SESSION:
mysql> select replace('u am a chump', 'u', 'i'); +---+
| replace('u am a chump', 'u', 'i') | +---+ | i am a chimp | +---+
REPEAT (str,x)
Returns a string co mpo sed o f string st r x times. INTERACTIVE SESSION:
mysql> select repeat('super ', 3); +---+
| repeat('super ', 3) | +---+ | super super super |
REVERSE(str)
Returns the reverse string o f st r. INTERACTIVE SESSION:
mysql> select reverse('evian'); +---+ | reverse('evian') | +---+ | naive | +---+
INSERT (str1,x,y,str2)
Inserts the string st r2 into the string st r1 at po sitio n x and replaces y characters fro m po sitio n x.
INTERACTIVE SESSION:
mysql> select insert('Powerful HeMan', 6, 3, 'less'); +---+
| insert('Powerful HeMan', 6, 3, 'less') | +---+ | Powerless HeMan | +---+
ELT (x,str1,str2,str3,...)
Returns string st r1 if x is 1, st r2 if x is 2, st r3 if x is 3 and so o n. If the index value is o ut o f range, returns NULL.
INTERACTIVE SESSION:
mysql> select elt(2, 'abc', 'def', 'ghi', 'jkl'); +---+
| elt(2, 'abc', 'def', 'ghi', 'jkl') | +---+ | def | +---+
mysql> select elt(4, 'abc', 'def', 'ghi', 'jkl'); +---+
| elt(4, 'abc', 'def', 'ghi', 'jkl') | +---+ | jkl | +---+
FIELD(str1,str2,str3,str4,...)
this functio n searches thro ugh string parameters fro m st r2 to the end fo r string st r1. If fo und, the index o f the string parameter is returned. Fo r instance, 1 is returned if st r2 is equal to st r1, 2 is returned if st r3 is equal to st r1 and so o n. If st r1 is no t fo und, a zero is returned.
INTERACTIVE SESSION:
mysql> select field('abc', 'abc', 'def', 'ghi', 'jkl'); +---+
| field('abc', 'abc', 'def', 'ghi', 'jkl') | +---+ | 1 | +---+
mysql> select field('blah', 'abc', 'def', 'ghi', 'jkl'); +---+
| field('blah', 'abc', 'def', 'ghi', 'jkl') | +---+ | 0 | +---+
T RIM([[BOT H | LEADING | T RAILING] [str2] FROM] str1)
INTERACTIVE SESSION:
mysql> select trim(' ab cd '); +---+
| trim(' ab cd ') | +---+ | ab cd | +---+
mysql> select trim(both ' ' from ' ab cd '); +---+
| trim(both ' ' from ' ab cd ') | +---+ | ab cd | +---+
mysql> select trim(both '?!' from '?!?!ab cd?!?!'); +---+
| trim(both '?!' from '?!?!ab cd?!?!') | +---+ | ab cd | +---+
mysql> select trim(leading '?!' from '?!?!ab cd?!?!'); +---+
| trim(leading '?!' from '?!?!ab cd?!?!') | +---+ | ab cd?!?! | +---+
mysql> select trim(trailing '?!' from '?!?!ab cd?!?!'); +---+
| trim(trailing '?!' from '?!?!ab cd?!?!') | +---+ | ?!?!ab cd | +---+