MySQL String Functions Part-3

ExtractValue Function:- ExtractValue Function is an XML function in MySQL. It retrieves data from XML content. It takes two arguments, the first argument will be text CDATA and the second argument will be Locator.

Syntax:- ExtractValue(xml_frag, xpath_expr)

Example:- SELECT ExtractValue(‘<a><b/></a>’, ‘/a/b’);

FIELD Function:- FIELD Function return the index value or position of a given string list.

This function performs case insensitive search

Syntax:- FIELD(value, str1, str2, str3, …)

SELECT FIELD(“4”, “1”, “4”, “7”, “10”);

output:- 2

SELECT FIELD(“h”, “d”, “e”, “H”, “h”);

Output:- 3

FIND_IN_SET Function:- FIND_IN_SET function find the position of a string within a list of string. In the case of a repeated string then the output will be the first occurrence string.

In case of an empty string or if a string is not found, It returns 0

In case of the list is NULL then it returns NULL

Syntax:- FIND_IN_SET(string, string_list)


SELECT FIND_IN_SET(“k”, “k, s, h, t, u, t, o, r”);

Output:- 1


 FORMAT Function:- The FORMAT Function is used to format the number and round them to a given decimal place. (“#,###,###.##”)

Syntax :- FORMAT(number, decimal_places)


SELECT FORMAT(358500.7394, 2);

Output:- 358,500.74

SELECT FORMAT(358500.7344, 2);

Output:- 358,500.73

INSERT Function:- INSERT Function inserted a string in a specific position within a string. It removes a number of characters from the original string.

Syntax:- INSERT(Original string, position, number of charector, inserted string)


SELECT INSERT(“kshtutor.com”, 1, 8, “srkp”);

Output:- srkp.com

SELECT INSERT(“kshtutor.com”, 10, 3, “in”);

Output:- kshtutor.in

 INSTR Function:- INSTR Function is used for searching in string. It returns an integer value. this integer value indicates the position of the first occurrence in a string.

It performs a case-insensitive search.

If the string will not be found it returns zero

Syntax:- INSTR(searching string, searched string)

For Example:-

SELECT INSTR(“kshtutor.com”, “or”);

output:- 7

SELECT INSTR(“PHP is a scripting Language”, “script”)


Share with:

Leave a Reply

Connect with: