Retorna el valor ASCII de un carcater
mysql> SELECT ASCII('2');
-> 50
mysql> SELECT ASCII(2);
-> 50
mysql> SELECT ASCII('dx');
-> 100
BIN(N)
Retorna valor binario de un decimal
mysql> SELECT BIN(12);
-> '1100'
BIT_LENGTH(str)
Retorna la longitud en Bits de un caracter
mysql> SELECT BIT_LENGTH('text');
-> 32
CHAR(N,... [USING charset_name])
Combierte numerico a caracter
mysql> SELECT CHAR(77,121,83,81,'76');
-> 'MySQL'
mysql> SELECT CHAR(77,77.3,'77.3');
-> 'MMM'
mysql> SELECT HEX(CHAR(1,0)), HEX(CHAR(256));
+----------------+----------------+
| HEX(CHAR(1,0)) | HEX(CHAR(256)) |
+----------------+----------------+
| 0100 | 0100 |
+----------------+----------------+
mysql> SELECT HEX(CHAR(1,0,0)), HEX(CHAR(256*256));
+------------------+--------------------+
| HEX(CHAR(1,0,0)) | HEX(CHAR(256*256)) |
+------------------+--------------------+
| 010000 | 010000 |
+------------------+--------------------+
mysql> SELECT CHARSET(CHAR(0x65)), CHARSET(CHAR(0x65 USING utf8));
+---------------------+--------------------------------+
| CHARSET(CHAR(0x65)) | CHARSET(CHAR(0x65 USING utf8)) |
+---------------------+--------------------------------+
| binary | utf8 |
+---------------------+--------------------------------+
CHAR_LENGTH(str)
Retorna la longitud del caracter
CHARACTER_LENGTH(str)
CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().
CONCAT(str1,str2,...)
Concatena caracteres o cadenas
SELECT CONCAT(CAST(int_col AS CHAR), char_col);
CONCAT() returns NULL if any argument is NULL.
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,...)
Concatena cadenas, el primer parametro es el separador de la concatenacion
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'
CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.
CONV(N,from_base,to_base)
Combierte de un sistema numerico a otro, el primer parámetro es el carácter el segundo de cual se convertira y el tercero el sistema que se devolvera
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'
ELT(N,str1,str2,str3,...)
Retorna el caracter o cadena que se establesca como primer parametro
mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo');
-> 'ej'
mysql> SELECT ELT(4, 'ej', 'Heja', 'hej', 'foo');
-> 'foo'
EXPORT_SET(bits,on,off[,separator[,number_of_bits]])
Returns a string such that for every bit set in the value bits, you get an on string and for every reset bit, you get an off string. Bits in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (the default being the comma character ‘,’). The number of bits examined is given by number_of_bits (defaults to 64).
mysql> SELECT EXPORT_SET(5,'Y','N',',',4);
-> 'Y,N,Y,N'
mysql> SELECT EXPORT_SET(6,'1','0',',',10);
-> '0,1,1,0,0,0,0,0,0,0'
FIELD(str,str1,str2,str3,...)
Retorna el numero de repeticiones que tiene el primer parametro
mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 2
mysql> SELECT FIELD('fo', 'Hej', 'ej', 'Heja', 'hej', 'foo');
-> 0
FIND_IN_SET(str,strlist)
Retorna el numero de matriz donde se encuentre el primer parametro
mysql> SELECT FIND_IN_SET('b','a,b,c,d');
-> 2
FORMAT(X,D)
Formato de decimales, el segundo parametro es el numero de decimales
mysql> SELECT FORMAT(12332.123456, 4);
-> '12,332.1235'
mysql> SELECT FORMAT(12332.1,4);
-> '12,332.1000'
mysql> SELECT FORMAT(12332.2,0);
-> '12,332'
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 representation of N_or_S where each character in N_or_S is converted to two hexadecimal digits.
mysql> SELECT HEX(255);
-> 'FF'
mysql> SELECT 0x616263;
-> 'abc'
mysql> SELECT HEX('abc');
-> 616263
INSERT(str,pos,len,newstr)
Inserta una cadena en otra
mysql> SELECT INSERT('Quadratic', 3, 4, 'What');
-> 'QuWhattic'
mysql> SELECT INSERT('Quadratic', -1, 4, 'What');
-> 'Quadratic'
mysql> SELECT INSERT('Quadratic', 3, 100, 'What');
-> 'QuWhat'
INSTR(str,substr)
Retorna la posicion buscada en la cadena
mysql> SELECT INSTR('foobarbar', 'bar');
-> 4
mysql> SELECT INSTR('xbar', 'foobar');
-> 0
LCASE(str)
LCASE() Combierte mayusculas a minusculas al igual que LOWER().
LEFT(str,len)
Subtrae caracteres de la izquierda
mysql> SELECT LEFT('foobarbar', 5);
-> 'fooba'
LENGTH(str)
Retorna el numero de caracteres
mysql> SELECT LENGTH('text');
-> 4
LOAD_FILE(file_name)
Reads the file and returns the file contents 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.
mysql> UPDATE t
SET blob_col=LOAD_FILE('/tmp/picture')
WHERE id=1;
LOCATE(substr,str), LOCATE(substr,str,pos)
Retorna la posicion de la cadena
mysql> SELECT LOCATE('bar', 'foobarbar');
-> 4
mysql> SELECT LOCATE('xbar', 'foobar');
-> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
-> 7
LOWER(str)
Combierte mayusculas a minusculas
mysql> SELECT LOWER('QUADRATICALLY');
-> 'quadratically'
LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.
mysql> SELECT LPAD('hi',4,'??');
-> '??hi'
mysql> SELECT LPAD('hi',1,'??');
-> 'h'
LTRIM(str)
Quita espacios en blanco
mysql> SELECT LTRIM(' barbar');
-> 'barbar'
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.
mysql> SELECT MAKE_SET(1,'a','b','c');
-> 'a'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice','world');
-> 'hello,world'
mysql> SELECT MAKE_SET(1 | 4,'hello','nice',NULL,'world');
-> 'hello'
mysql> SELECT MAKE_SET(0,'a','b','c');
-> ''
MID(str,pos,len)
MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).
OCT(N)
Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL.
mysql> SELECT OCT(12);
-> '14'
OCTET_LENGTH(str)
OCTET_LENGTH() is a synonym for LENGTH().
ORD(str)
If the leftmost character of the string str is a multi-byte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:
(1st byte code)
+ (2nd byte code × 256)
+ (3rd byte code × 2562) ...
If the leftmost character is not a multi-byte character, ORD() returns the same value as the ASCII() function.
mysql> SELECT ORD('2');
-> 50
POSITION(substr IN str)
POSITION(substr IN str) is a synonym for LOCATE(substr,str).
QUOTE(str)
Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotes and with each instance of single quote (‘'’), backslash (‘\’), ASCII NUL, and Control-Z preceded by a backslash. If the argument is NULL, the return value is the word “NULL” without enclosing single quotes.
mysql> SELECT QUOTE('Don\'t!');
-> 'Don\'t!'
mysql> SELECT QUOTE(NULL);
-> NULL
REPEAT(str,count)
Repite cadenas
mysql> SELECT REPEAT('MySQL', 3);
-> 'MySQLMySQLMySQL'
REPLACE(str,from_str,to_str)
Remplaza una cadena por otra
mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');
-> 'WwWwWw.mysql.com'
REVERSE(str)
Invierte una cadena
mysql> SELECT REVERSE('abc');
-> 'cba'
RIGHT(str,len)
Retorna cadena de derecha a izquierda
mysql> SELECT RIGHT('foobarbar', 4);
-> 'rbar'
RPAD(str,len,padstr)
Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.
mysql> SELECT RPAD('hi',5,'?');
-> 'hi???'
mysql> SELECT RPAD('hi',1,'?');
-> 'h'
RTRIM(str)
Quita espacios en blanco por la izquierda
mysql> SELECT RTRIM('barbar ');
-> 'barbar'
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 four 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-alphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.
Important: When using SOUNDEX(), you should be aware of the following limitations:
This function, as currently implemented, is intended to work well with strings that are in the English language only. Strings in other languages may not produce reliable results.
This function is not guaranteed to provide consistent results with strings that use multi-byte character sets, including utf-8.
We hope to remove these limitations in a future release. See Bug#22638 for more information.
mysql> SELECT SOUNDEX('Hello');
-> 'H400'
mysql> SELECT SOUNDEX('Quadratically');
-> 'Q36324'
Note: This function implements the original Soundex algorithm, not the more popular enhanced version (also described by D. Knuth). The difference is that original version discards vowels first and duplicates second, whereas the enhanced version discards duplicates first and vowels second.
expr1 SOUNDS LIKE expr2
This is the same as SOUNDEX(expr1) = SOUNDEX(expr2).
SPACE(N)
Retorna espacios en blanco.
mysql> SELECT SPACE(6);
-> ' '
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
Substrae una cadena de otra
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'
SUBSTRING_INDEX(str,delim,count)
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
-> 'www.mysql'
mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
-> 'mysql.com'
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
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'
UCASE(str)
UCASE() is a synonym for UPPER().
UNHEX(str)
Performs the inverse operation of HEX(str). That is, it interprets each pair of hexadecimal digits in the argument as a number and converts it to the character represented by the number. The resulting characters are returned as a binary string.
mysql> SELECT UNHEX('4D7953514C');
-> 'MySQL'
mysql> SELECT 0x4D7953514C;
-> 'MySQL'
mysql> SELECT UNHEX(HEX('string'));
-> 'string'
mysql> SELECT HEX(UNHEX('1267'));
-> '1267'
UPPER(str)
mysql> SELECT UPPER('Hej');
-> 'HEJ'
No hay comentarios:
Publicar un comentario