|
Home
/ Technical Support / MySQL
Key Offerings:
B2B and B2C E-Business
Solutions
Offshore
Software Development Outsourcing
Strategic Consulting
Offshore Software Outsourcing
ALTOROS Systems
is headquartered in Tampa, Florida and maintains an office
near Boston, Massachusetts and technology development center
in Belarus and Russia. ALTOROS specializes on providing value-added
e-commerce and web-based software development and offshore
software outsourcing services to emerging enterprises helping
them successfully plan and implement business initiatives.
Contact Us for more
information.
String Functions
String-valued functions return NULL if the length of the result would be greater than the max_allowed_packet server parameter. See Server parameters.
For functions that operate on string positions, the first position is numbered 1.
ASCII(str)
Returns the ASCII code value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL:
mysql> SELECT ASCII('2');
-> 50
mysql> SELECT ASCII(2);
-> 50
mysql> SELECT ASCII('dx');
-> 100
See also the ORD() function.
ORD(str)
If the leftmost character of the string str is a multi-byte character, returns the code for that character, calculated from the ASCII code values of its constituent characters using this formula: ((first byte ASCII code)*256+(second byte ASCII code))[*256+third byte ASCII code...]. If the leftmost character is not a multi-byte character, returns the same value that the ASCII() function does:
mysql> SELECT ORD('2');
-> 50
CONV(N,from_base,to_base)
Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV works with 64-bit precision:
mysql> SELECT CONV("a",16,2);
-> '1010'
mysql> SELECT CONV("6E",18,8);
-> '172'
mysql> SELECT CONV(-17,10,-18);
-> '-H'
mysql> SELECT CONV(10+"10"+'10'+0xa,10,10);
-> '40'
BIN(N)
Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL:
mysql> SELECT BIN(12);
-> '1100'
OCT(N)
Returns a string representation of the octal value of N, where N is a longlong number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL:
mysql> SELECT OCT(12);
-> '14'
HEX(N_or_S)
If N_OR_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16).
If N_OR_S is a string, returns a hexadecimal string of N_OR_S where each character in N_OR_S is converted to 2 hexadecimal digits. This is the invers of the 0xff strings.
mysql> SELECT HEX(255);
-> 'FF'
mysql> SELECT HEX("abc");
-> 616263
mysql> SELECT 0x616263;
-> "abc"
CHAR(N,...)
CHAR() interprets the arguments as integers and returns a string consisting of the characters given by the ASCII code values of those integers. NULL values are skipped:
mysql> SELECT CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
-> 'MMM'
CONCAT(str1,str2,...)
Returns the string that results from concatenating the arguments. Returns NULL if any argument is NULL. May have more than 2 arguments. A numeric argument is converted to the equivalent string form:
mysql> SELECT CONCAT('My', 'S', 'QL');
-> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');
-> NULL
mysql> SELECT CONCAT(14.3);
-> '14.3'
CONCAT_WS(separator, str1, str2,...)
CONCAT_WS() stands for CONCAT With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator can be a string as well as the rest of the arguments. If the separator is NULL, the result will be NULL. The function will skip any NULLs and empty strings, after the separator argument. The separator will be added between the strings to be concatenated:
mysql> SELECT CONCAT_WS(",","First name","Second name","Last Name");
-> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(",","First name",NULL,"Last Name");
-> 'First name,Last Name'
LENGTH(str)
OCTET_LENGTH(str)
CHAR_LENGTH(str)
CHARACTER_LENGTH(str)
Returns the length of the string str:
mysql> SELECT LENGTH('text');
-> 4
mysql> SELECT OCTET_LENGTH('text');
-> 4
Note that for CHAR_LENGTH() and CHARACTER_LENGTH(), multi-byte characters are only counted once.
BIT_LENGTH(str)
Returns the length of the string str in bits:
mysql> SELECT BIT_LENGTH('text');
-> 32
LOCATE(substr,str)
POSITION(substr IN str)
Returns the position of the first occurrence of substring substr in string str. Returns 0 if substr is not in str:
mysql> SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
This function is multi-byte safe. In MySQL 3.23 this function is case sensitive, while in 4.0 it's only case-sensitive if either argument is a binary string.
LOCATE(substr,str,pos)
Returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str:
mysql> SELECT LOCATE('bar', 'foobarbar',5);
-> 7
This function is multi-byte safe. In MySQL 3.23 this function is case sensitive, while in 4.0 it's only case-sensitive if either argument is a binary string.
INSTR(str,substr)
Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the arguments are swapped:
mysql> SELECT INSTR('foobarbar', 'bar');
-> 4
mysql> SELECT INSTR('xbar', 'foobar');
-> 0
This function is multi-byte safe. In MySQL 3.23 this function is case sensitive, while in 4.0 it's only case-sensitive if either argument is a binary string.
LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr until str is len characters long. If str is longer than len' then it will be shortened to len characters.
mysql> SELECT LPAD('hi',4,'??');
-> '??hi'
RPAD(str,len,padstr)
Returns the string str, right-padded with the string padstr until str is len characters long. If str is longer than len' then it will be shortened to len characters.
mysql> SELECT RPAD('hi',5,'?');
-> 'hi???'
LEFT(str,len)
Returns the leftmost len characters from the string str:
mysql> SELECT LEFT('foobarbar', 5);
-> 'fooba'
This function is multi-byte safe.
RIGHT(str,len)
Returns the rightmost len characters from the string str:
mysql> SELECT RIGHT('foobarbar', 4);
-> 'rbar'
This function is multi-byte safe.
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
MID(str,pos,len)
Returns a substring len characters long from string str, starting at position pos. The variant form that uses FROM is ANSI SQL92 syntax:
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
This function is multi-byte safe.
SUBSTRING()
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
Returns a substring from string str starting at position pos:
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
This function is multi-byte safe.
SUBSTRING_INDEX()
SUBSTRING_INDEX(str,delim,count)
Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned:
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
This function is multi-byte safe.
LTRIM()
LTRIM(str)
Returns the string str with leading space characters removed:
mysql> SELECT LTRIM(' barbar');
-> 'barbar'
RTRIM()
RTRIM(str)
Returns the string str with trailing space characters removed:
mysql> SELECT RTRIM('barbar ');
-> 'barbar'
This function is multi-byte safe.
TRIM()
TRIM([[BOTH | LEADING | TRAILING] [remstr] FROM] str)
Returns the string str with all remstr prefixes and/or suffixes removed. If none of the specifiers BOTH, LEADING or TRAILING are given, BOTH is assumed. If remstr is not specified, spaces are removed:
mysql> SELECT TRIM(' bar ');
-> 'bar'
mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');
-> 'barxxx'
mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');
-> 'bar'
mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');
-> 'barx'
This function is multi-byte safe.
SOUNDEX()
SOUNDEX(str)
Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is 4 characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All non-alphanumeric characters are ignored in the given string. All international alpha characters outside the A-Z range are treated as vowels:
mysql> SELECT SOUNDEX('Hello');
-> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
-> 'Q36324'
SPACE()
SPACE(N)
Returns a string consisting of N space characters:
mysql> SELECT SPACE(6);
-> ' '
REPLACE()
REPLACE(str,from_str,to_str)
Returns the string str with all all occurrences of the string from_str replaced by the string to_str:
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
This function is multi-byte safe.
REPEAT()
REPEAT(str,count)
Returns a string consisting of the string str repeated count times. If count <= 0, returns an empty string. Returns NULL if str or count are NULL:
mysql> SELECT REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
REVERSE()
REVERSE(str)
Returns the string str with the order of the characters reversed:
mysql> SELECT REVERSE('abc');
-> 'cba'
This function is multi-byte safe.
INSERT()
INSERT(str,pos,len,newstr)
Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr:
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
This function is multi-byte safe.
ELT()
ELT(N,str1,str2,str3,...)
Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD():
mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
-> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
-> 'foo'
FIELD()
FIELD(str,str1,str2,str3,...)
Returns the index of str in the str1, str2, str3, ... list. Returns 0 if str is not found. FIELD() is the complement of ELT():
mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 2
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 0
FIND_IN_SET()
FIND_IN_SET(str,strlist)
Returns a value 1 to N if the string str is in the list strlist consisting of N substrings. A string list is a string composed of substrings separated by , characters. If the first argument is a constant string and the second is a column of type SET, the FIND_IN_SET() function is optimised to use bit arithmetic! Returns 0 if str is not in strlist or if strlist is the empty string. Returns NULL if either argument is NULL. This function will not work properly if the first argument contains a ,:
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
MAKE_SET()
MAKE_SET(bits,str1,str2,...)
Returns a set (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, etc. NULL strings in str1, str2, ... are not appended to the result:
mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(0,'a','b','c');
-> ''
EXPORT_SET()
EXPORT_SET(bits,on,off,[separator,[number_of_bits]])
Returns a string where for every bit set in 'bit', you get an 'on' string and for every reset bit you get an 'off' string. Each string is separated with 'separator' (default ',') and only 'number_of_bits' (default 64) of 'bits' is used:
mysql> SELECT EXPORT_SET(5,'Y','N',',',4)
-> Y,N,Y,N
LOWER();LCASE()
LCASE(str)
LOWER(str)
Returns the string str with all characters changed to lowercase according to the current character set mapping (the default is ISO-8859-1 Latin1):
mysql> SELECT LCASE('QUADRATICALLY');
-> 'quadratically'
This function is multi-byte safe.
UPPER();UCASE()
UCASE(str)
UPPER(str)
Returns the string str with all characters changed to uppercase according to the current character set mapping (the default is ISO-8859-1 Latin1):
mysql> SELECT UCASE('Hej');
-> 'HEJ'
This function is multi-byte safe.
FILE;LOAD_FILE()
LOAD_FILE(file_name)
Reads the file and returns the file contents as a string. The file must be on the server, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and be smaller than max_allowed_packet.
If the file doesn't exist or can't be read due to one of the above reasons, the function returns NULL:
mysql> UPDATE tbl_name
SET blob_column=LOAD_FILE("/tmp/picture")
WHERE id=1;
If you are not using MySQL Version 3.23, you have to do the reading of the file inside your application and create an INSERT statement to update the database with the file information. One way to do this, if you are using the MySQL++ library, can be found at `http://www.mysql.com/documentation/mysql++/mysql++-examples.html'.
MySQL automatically converts numbers to strings as necessary, and vice-versa:
mysql> SELECT 1+"1";
-> 2
mysql> SELECT CONCAT(2,' test');
-> '2 test'
If you want to convert a number to a string explicitly, pass it as the argument to CONCAT().
If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This only affects comparisons.
|
