MySQL String Functions Part-5

 MATCH Function:- The MATCH Function is used for full-text searches. It performs a case-insensitive search. It is required to make a full-text index on the table before performing a full-text search. InnoDB and MyISAM engine tables can use full-text index. The full-text data type can be CHAR, VARCHAR, and TEXT. It accepts a comma-separated list of table columns to be searched.

Syntax:- MATCH (col1,col2, col4, col5…) AGAINST (expression [search_modifier])

For Example:-

SELECT EmpId, EmpName, Empsummery FROM Employee WHERE MATCH(Empsummery) AGAINST(‘skill’);

SELECT storiID, title, description FROM Book WHERE MATCH(title, description) AGAINST(‘Novel’);

 MID Function:- MID Function is used to extract a substring from the given string. It takes three arguments.

  1. String:- Main string from which substring will extract.
  2. Position:- If the position will be negative then extraction will start from the end. If the position will positive then extraction will start from the beginning.
  3. Length:- It indicates how long the string will extract.

Important thing SUBSTR() Function and SUBSTRING() Function are similar to MID() Function.


MID(string, Position, Length)

For Example:-

SELECT MID(“Mysql function”, 4, 6)

Output:- ql fun


Output:- OR;

POSITION Function:- POSITION Function is used for the first occurrence position of a substring from a given string. From the original string, if the substring will not found then it will return zero. It performs a case-insensitive search.

LOCATE() Function and POSITION() Function are the same.

Syntax:- LOCATE(substring, IN, String)

For Example:-

SELECT POSITION(“tutor” IN “kshtutor.com”)

Output:- 4

SELECT POSITION(“o” IN “Ram is a good boy”)

Output:- 11

REPEAT Function:- The REPEAT Function is used to repeat strings a given number of times.

Syntax:- REPEAT(string, count)

For Examole:-

SELECT REPEAT(“Tutor”, 4);

Outpot:- TutorTutorTutorTutor

 REPLACE Function:- REPLACE Function is used for replacing all occurrences of a substring from a given string with a new string. If the search string will not found then it will return the original string. It is case-sensitive.

Syntax:- REPLACE(String, find String, Replace String)

For Example:-

SELECT REPLACE(“KSH Tutor”, “KSH”, “srkp”);

Output:- srkp Tutor

SELECT REPLACE(“KSH Tutor”, “ksh”, “srkp”);

Output:- KSH Tutor

REVERSE Function:- REVERSE Function is used for reversing the order of a given string.

Syntax:- REVERSE(string)

For Example:-


Output:- rotuT HSK

 RIGHT Function:- RIGHT Function is used for extracting string from a given number of characters from the right side of the string. If the number of characters will be more than the given length then it will return the original string.

Syntax:-  RIGHT(string, Number of Character)

For Example:-

SELECT RIGHT(“kshtutor is nice.”, 5)

Output:- nice.

SELECT RIGHT(“kshtutor is nice.”, 25)

Output:- kshtutor is nice.

Share with:

Leave a Reply

Connect with: