Notes

MYSQL STRING FUNCTIONS

-- select bin(46) -> returns in binary form
-- select ascii('ab') -> returns ascii number of left most number or char supplied
-- SELECT char_length('dkkfhdh3r4hfhafa') -> returns no. of characters present in a supplied string
-- SELECT CONCAT('My', 'shaf' ,'S', 'QL') -> concatenates all the strings into a single string
-- SELECT CONCAT_WS(' % ','First name',NULL,'Last Name', 'shafi') -> concatenates all the string with a user defined separator as a first argument
-- SELECT ELT(5, 'Aa', 'Bb', 'Cc', 34, 'shafi') -> returns the Nth element of the list of strings
-- SELECT FIELD('Shafi', 'Aa', 'shafi','Bb', 'Cc', 'Dd', 'Ff'); -> looks for 1st argument in the remaining arguments
-- SELECT FIND_IN_SET('b','a,b,c,d') -> same as field() but it looks in a single set only
-- SELECT INSERT('Quadratic', 3, 100, 'What') -> adds what from 3rd position to next 100 positions
-- select instr('djfkshafjfjdlfjajasha','sha') -> Returns the position of the first occurrence of substring substr in string
-- lcase('sSHAI'), LOWER('SHFA'), UPPER('shaf'), ucase('adfha) -> converts lower case to upper and vice versa
-- SELECT REPEAT('MySQL', 3) = MySQLMySQLMySQL
-- SELECT REPLACE('www.mysql.com', 'w', 'vv') = vvvvvv.mysql.com
-- SELECT REVERSE('abc df') =fd cba
-- SELECT TRIM(both/trailing/leading 'x' FROM 'xxxbarxxx') to remove extra spaces ltrim(),rtrim() are alternatives
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);