MySQL String Functions Part-5

In MySQL String Functions Part-5 I shall discuss the MATCH Function, MID Function, POSITION Function, REPEAT Function, REPLACE Function, REVERSE Function, and RIGHT Function.

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 Example:-

SELECT REPEAT(“Tutor”, 4);

Output:- 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 a 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.

Read Also:-

MySQL String Functions Part-1

MySQL String Functions Part-2

MySQL String Functions Part-3

MySQL String Functions Part-4

MySQL String Functions Part-6

Read more detail about MySQL String function manual

Share with:

One thought on “MySQL String Functions Part-5

  1. Pingback: Highly informative it is a good MySQL study

Leave a Reply

Connect with:

Verified by ExactMetrics