MySQL String Functions Part-3

In MySQL String Functions Part-3 I shall discuss the ExtractValue Function, FIELD Function, FIND_IN_SET Function, FORMAT Function, INSERT Function, and INSTR Function.

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 returns 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 finds the position of a string within a list of strings. 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”)


Read Also:-

MySQL String Functions Part-1

MySQL String Functions Part-2

MySQL String Functions Part-4

MySQL String Functions Part-5

MySQL String Functions Part-6

Read more detail about MySQL String function manual

Share with:

One thought on “MySQL String Functions Part-3

  1. Pingback: Very informatics and knowledgeable article

Leave a Reply

Connect with:

Verified by ExactMetrics