MySQL text manipulation functions

Explain the following functions with an example.

left(), length(), locate(), lower(), LTRIM(), Right(), RTRIM(), Soundex(), Substring(), Upper()

left() : Returns the leftmost “n” characters from the string str, or NULL if any argument is NULL.

Example:
Select LEFT (‘samplestring’,’4’);

Output:
samp

length() : Returns the length of the string str measured in bytes.

Example:
Select length (‘sample’);

Output:
6

locate() : Used to return the position of the first occurrence of the substring substr in string str. Returns 0 if not found

Example:
select LOCATE(‘sample’,’thesample’);

Output
3

lower() : Returns the characters in lower case

Example:
select LOWER(‘SAMPLE’);

Output:
sample

LTRIM() : Returns the string str with leading space characters removed

Example:
Select LTRIM(‘ sample’);

Output:
sample

Right() : Returns the rightmost n characters from the string str, or NULL if any argument is NULL

Example:
Select RIGHT(‘sample’,’3’);

Output:
mple

RTRIM() : Returns the string str with trailing space characters removed

Example:
Select RTRIM(‘sample‘);

Output:
sample

Soundex() : Soundex strings are strings that sound almost the same. Two strings that sound almost the same should have identical soundex strings

Example:
Select SOUNDEX(‘hello’);

Output:
H400

Substring() : There are many forms of the function Substring(). The forms without a len argument return a substring from string str starting at position pos.

Example:
Select SUBSTRING(‘sample’,’2’);

Output:
Ample

Upper() : Returns the string str with all characters changed to uppercase according to the current character set mapping

Example:
Select UPPER(‘Hello’);

Output:
HELLO
MySQL date time manipulation functions
MySQL date time manipulation functions - AddDate(), AddTime(),CurDate(),CurTime(),Date(), DateDiff(), Date_Add(), Date_format()
MySQL numeric manipulation functions
MySQL numeric manipulation functions - Abs(), Cos(), Exp(), Rand(), Sin(), Sqrt(), Tan(), Mod(), Pi()
MySQL Group By
MySQL Group By is used along with aggregate functions to group the result-set by one or more columns.
Post your comment

You may like