MySQL String functions
UPPER(), UCASE()
Returns the string with all characters changed to uppercase.
SELECT dept_no, UPPER(dept_name) FROM departments; SELECT dept_no, UCASE(dept_name) FROM departments;
LOWER(), LCASE()
Returns the string with all characters changed to lowercase.
SELECT dept_no, LOWER(dept_name) FROM departments; SELECT dept_no, LCASE(dept_name) FROM departments;
CHAR_LENGTH(str)
Returns the length of the string str, measured in characters.
SELECT dept_name, CHAR_LENGTH(dept_name) FROM departments;
LTRIM(str)
Returns the string str with leading space characters removed but not in the end or in between.
RTRIM(str)
Returns the string str with trailing space characters removed but not in the beginning or in between.
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.
SELECT LTRIM(' Hello');
SELECT RTRIM(' Hello ');
SELECT TRIM(' Hello ');
SELECT TRIM(LEADING '-' FROM '--Hello--');
SELECT TRIM(TRAILING '-' FROM '--Hello--');
SELECT TRIM('-' FROM '--Hello--');
REVERSE()
Returns the string str with the order of the characters reversed.
SELECT REVERSE(dept_no), dept_no FROM departments;
REPLACE(str,from_str,to_str)
Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.
SELECT REPLACE('j2ee development', 'j2ee', 'Java EE');
Substring
SUBSTRING(str,pos)
SUBSTRING(str FROM pos)
SUBSTRING(str,pos,len)
SUBSTRING(str FROM pos FOR len)
Return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning.The position of the first character in the string from which the substring is to be extracted is reckoned as 1.
SELECT SUBSTRING('Java EE development', 3);
SELECT SUBSTRING('Java EE development' FROM 4);
SELECT SUBSTRING('Java EE development', 3, 8);
SELECT SUBSTRING('Java EE development' FROM 4 FOR 8);
STRCMP(expr1,expr2)
STRCMP() returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.
SELECT STRCMP('Hello', 'Bye');
SELECT STRCMP('Hi', 'Welcome!');
SELECT STRCMP('Hi', 'Hi');
FORMAT(X,D[,locale])
Formats the number X to a format like ‘#,###,###.##’, rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.
The optional third parameter enables a locale to be specified to be used for the result number’s decimal point, thousands separator, and grouping between separators.
SELECT FORMAT(12345653.3456, 3); SELECT FORMAT(12345653.3456, 3, 'en_IN');
‘en-IN’ is the Locale for India.
CONCAT(str1,str2,…)
Returns the string that results from concatenating the arguments. May have one or more arguments.
SELECT ssn, CONCAT('$', salary, '/-') FROM employee;
