CONCAT(): SELECT CONCAT(first,' ',last) AS fullname FROM users; : Combine two or more strings
together
CONCAT_WS(): SELECT CONCAT_WS('-',year,month,day) FROM dates; : Concatenate with a separator
automatically
SUBSTRING(): SELECT SUBSTRING(code,1,3) FROM sku; : Extract substring from a position
LEFT(): SELECT LEFT(name,5) FROM customers; : Get leftmost characters
RIGHT(): SELECT RIGHT(name,4) FROM customers; : Get rightmost characters
LENGTH(): SELECT LENGTH(title) FROM books; : Return byte length of string
CHAR_LENGTH(): SELECT CHAR_LENGTH(title) FROM books; : Return number of characters
OCTET_LENGTH(): SELECT OCTET_LENGTH(title) FROM books; : Return length in bytes per multibyte
char
TRIM(): SELECT TRIM(' xyz ') AS t; : Remove leading and trailing spaces
LTRIM(): SELECT LTRIM(' abc'); : Remove leading spaces
RTRIM(): SELECT RTRIM('abc '); : Remove trailing spaces
LOWER(): SELECT LOWER(city) FROM locations; : Convert text to lowercase
UPPER(): SELECT UPPER(city) FROM locations; : Convert text to uppercase
REPLACE(): SELECT REPLACE(txt,'foo','bar') FROM msgs; : Replace occurrences of substring
INSERT(): SELECT INSERT('ABCDE',3,2,'--') AS new; : Replace part of string starting at position
SUBSTRING_INDEX(): SELECT SUBSTRING_INDEX(email,'@',1) FROM users; : Return substring before or
after delimiter
LOCATE(): SELECT LOCATE('ab',text) FROM docs; : Find position of substring
INSTR(): SELECT INSTR(text,'ab') FROM docs; : Same as LOCATE but arg order reversed
POSITION(): SELECT POSITION('ab' IN text) FROM docs; : ANSI alternative to LOCATE
REPEAT(): SELECT REPEAT('*',5); : Repeat a string N times
REVERSE(): SELECT REVERSE(name) FROM people; : Return string with characters reversed
LPAD(): SELECT LPAD(num,4,'0') FROM seq; : Pad string on left to target length
RPAD(): SELECT RPAD(num,4,'0') FROM seq; : Pad on right
FORMAT(): SELECT FORMAT(12345.678,2); : Format number with locale commas
QUOTE(): SELECT QUOTE("O'Reilly"); : Escape string for SQL
ELT(): SELECT ELT(2,'x','y','z'); : Return Nth element of list
FIELD(): SELECT FIELD('y','x','y','z'); : Return position of value in list
MAKE_SET(): SELECT MAKE_SET(5,'a','b','c','d'); : Return set of strings for bits set
FIND_IN_SET(): SELECT FIND_IN_SET('b','a,b,c'); : Locate string in comma-separated list
SOUNDEX(): SELECT SOUNDEX('juice'); : Return phonetic code for string
DIFFERENCE(): SELECT DIFFERENCE('juice','juce'); : Compare Soundex codes (SQL Server)
HEX( ): SELECT HEX('abc'); : Convert string to hexadecimal
UNHEX(): SELECT UNHEX('616263'); : Convert hex to binary string
UUID(): SELECT UUID(); : Generate a universally unique identifier
SPACE(): SELECT CONCAT('Hi',SPACE(3),'Bye'); : Insert N spaces
CHAR(): SELECT CHAR(65,66,67); : Return characters from ASCII codes
CHARSET(): SELECT CHARSET('abc'); : Show string's character set
COLLATION(): SELECT COLLATION('abc'); : Show collation used for comparison
MD5(): SELECT MD5('password'); : Generate 128-bit hash of string
SHA2(): SELECT SHA2('password',256); : Generate SHA-2 hash of specified length
together
CONCAT_WS(): SELECT CONCAT_WS('-',year,month,day) FROM dates; : Concatenate with a separator
automatically
SUBSTRING(): SELECT SUBSTRING(code,1,3) FROM sku; : Extract substring from a position
LEFT(): SELECT LEFT(name,5) FROM customers; : Get leftmost characters
RIGHT(): SELECT RIGHT(name,4) FROM customers; : Get rightmost characters
LENGTH(): SELECT LENGTH(title) FROM books; : Return byte length of string
CHAR_LENGTH(): SELECT CHAR_LENGTH(title) FROM books; : Return number of characters
OCTET_LENGTH(): SELECT OCTET_LENGTH(title) FROM books; : Return length in bytes per multibyte
char
TRIM(): SELECT TRIM(' xyz ') AS t; : Remove leading and trailing spaces
LTRIM(): SELECT LTRIM(' abc'); : Remove leading spaces
RTRIM(): SELECT RTRIM('abc '); : Remove trailing spaces
LOWER(): SELECT LOWER(city) FROM locations; : Convert text to lowercase
UPPER(): SELECT UPPER(city) FROM locations; : Convert text to uppercase
REPLACE(): SELECT REPLACE(txt,'foo','bar') FROM msgs; : Replace occurrences of substring
INSERT(): SELECT INSERT('ABCDE',3,2,'--') AS new; : Replace part of string starting at position
SUBSTRING_INDEX(): SELECT SUBSTRING_INDEX(email,'@',1) FROM users; : Return substring before or
after delimiter
LOCATE(): SELECT LOCATE('ab',text) FROM docs; : Find position of substring
INSTR(): SELECT INSTR(text,'ab') FROM docs; : Same as LOCATE but arg order reversed
POSITION(): SELECT POSITION('ab' IN text) FROM docs; : ANSI alternative to LOCATE
REPEAT(): SELECT REPEAT('*',5); : Repeat a string N times
REVERSE(): SELECT REVERSE(name) FROM people; : Return string with characters reversed
LPAD(): SELECT LPAD(num,4,'0') FROM seq; : Pad string on left to target length
RPAD(): SELECT RPAD(num,4,'0') FROM seq; : Pad on right
FORMAT(): SELECT FORMAT(12345.678,2); : Format number with locale commas
QUOTE(): SELECT QUOTE("O'Reilly"); : Escape string for SQL
ELT(): SELECT ELT(2,'x','y','z'); : Return Nth element of list
FIELD(): SELECT FIELD('y','x','y','z'); : Return position of value in list
MAKE_SET(): SELECT MAKE_SET(5,'a','b','c','d'); : Return set of strings for bits set
FIND_IN_SET(): SELECT FIND_IN_SET('b','a,b,c'); : Locate string in comma-separated list
SOUNDEX(): SELECT SOUNDEX('juice'); : Return phonetic code for string
DIFFERENCE(): SELECT DIFFERENCE('juice','juce'); : Compare Soundex codes (SQL Server)
HEX( ): SELECT HEX('abc'); : Convert string to hexadecimal
UNHEX(): SELECT UNHEX('616263'); : Convert hex to binary string
UUID(): SELECT UUID(); : Generate a universally unique identifier
SPACE(): SELECT CONCAT('Hi',SPACE(3),'Bye'); : Insert N spaces
CHAR(): SELECT CHAR(65,66,67); : Return characters from ASCII codes
CHARSET(): SELECT CHARSET('abc'); : Show string's character set
COLLATION(): SELECT COLLATION('abc'); : Show collation used for comparison
MD5(): SELECT MD5('password'); : Generate 128-bit hash of string
SHA2(): SELECT SHA2('password',256); : Generate SHA-2 hash of specified length