MENU


String

Numeric Conversion
SELECT ASCII('abc'); -- code for 'a' 97
SELECT ORD('好'); -- multibyte code 15050173
SELECT CHAR(77,121,83,81,'76'); MySQL
SELECT BIN(13); 1101
SELECT OCT(51); 51
SELECT HEX(47); 2F
SELECT UNHEX('4D7953514C'),  0x4D7953514C; MySQL MySQL
SELECT FORMAT(123456.123456, 4); 1,234.1235

Length
SELECT LENGTH('abc永'); 6
SELECT BIT_LENGTH('abc永'); 48
SELECT OCTET_LENGTH('abc永'); 6
SELECT CHAR_LENGTH('abc永'); 4
SELECT CHARACTER_LENGTH('abc永'); 4
SELECT HEX(WEIGHT_STRING('永'));
-- comparison value		 6C38

Comparison
SELECT STRCMP('abc','def'),STRCMP('def','abc'); -1 1
SELECT STRCMP('abc','abc'); 0
SELECT STRCMP('abc','abcd');
-- abc precedes abcd		 -1
Case
SELECT UCASE('aBcd'), UPPER('EFgh');
-- synonyms		 ABCD EFGH
SELECT LCASE('aBcd'), LOWER('EFgh');
-- synonyms		 abcd efgh

Spacing and Padding
SELECT SPACE(5); ‘     ‘
SELECT TRIM('  abc  '); ‘abc’
SELECT LTRIM('  abc  '); ‘abc  ‘
SELECT RTRIM('  abc  '); ‘  abc’
SELECT LPAD('abcd',7,'--'); ---abcd
SELECT RPAD('abcd',3,'--'); abc

Concatenation
SELECT CONCAT('ab',12,'cd'); ab12cd
SELECT CONCAT('ab',12,NULL); NULL
SELECT CONCAT_WS(',','ab',12,'cd'); ab,12,cd
SELECT CONCAT_WS(',','ab',12,NULL); ab,12
SELECT REPEAT('abc',3); abcabcabc

Arguments Set
SELECT ELT(2,'ab','cd','ef'); cd
SELECT EXPORT_SET(6,'1','0','|',8);
-- 6 is 0110		 0|1|1|0|0|0|0|0
SELECT MAKE_SET(5,'ab','cd','ef');
-- 5 is 101		 ab,ef
SELECT FIELD('cd','ab','cd','ef','gh');
-- cd is the second one		 2
SELECT FIND_IN_SET('cd','ab,cd,ef,gh');
-- cd is the second one		 2

Substring
SELECT LOCATE('cd','abcdef');
-- synonym: POSITION()		 3
SELECT LOCATE('cd','abcdefcd',4); 7
SELECT LOCATE('zz','abcdefcd',4); 0
SELECT INSTR('abcdef','cd'); 3
SELECT LEFT('abcdef',3); abc
SELECT RIGHT('abcdef',3); def
SELECT SUBSTR('abcdef',3);
-- synonym: SUBSTRING()		 cdef
SELECT SUBSTR('abcdef' FROM 3); cdef
SELECT SUBSTR('abcdef', 3,2); cd
SELECT SUBSTR('abcdef' FROM 3 FOR 2); cd
SELECT SUBSTR('abcdef',-2); ef
SELECT INSERT('abcdef',3,2,'XXXX'); abXXXXef
SELECT REPLACE('abcdabef','ab','XX'); XXcdXXef
SELECT REVERSE('abcd'); dbca
SELECT SUBSTRING_INDEX('www.google.com','.',2); www.google
SELECT SUBSTRING_INDEX('www.google.com','.',-2); google.com

Miscellaneous
SELECT '\\\'',QUOTE('\\\''); \’
‘\\\’’
SELECT SOUNDEX('hello'),SOUNDEX('helo'); H400  H400
SELECT 'hello' SOUNDS LIKE 'helo'; 1
SELECT TO_BASE64('xyz'), FROM_BASE64(TO_BASE64('xyz')); eHI6
xyz
SELECT LOAD_FILE(‘file.txt’); <file content>