MySQL regexp_instr() function

MySQL regexp_instr() function for beginners and professionals on mysql tutorial, examples, functions, programming, mysql, literals, cursor, procedure, trigger, regexp_like(), regexp_replace operator, regular expression, regexp_instr(), crud etc.

<< Back to MYSQL

next → ← prev

MySQL REGEXP_INSTR() Function

REGEXP_INSTR() function in MySQL is used for pattern matching. It is a function that returns the index value of the substring by matching the given string. This function returns 0 if no match is found. Else, it returns 1. If either expression or pattern is NULL, it returns NULL. Here the indexing of characters starts at 1.

Syntax

The following is a basic syntax to use this function in MySQL:

REGEXP_INSTR(expression, patterns, optional parameters);

Parameter Explanation

The explanation of the REGEXP_INSTR() function parameters are:

expression: It is a string on which we will perform searching through regular expressions parameters and functions.

patterns: It represents the regular expression character to be used for matching.

The REGEXP_INSTR() function uses various optional parameters that are given below:

pos: It is used to specify the position in expression at which the search will starts. If we will not specify this parameter, by default, it is 1.

occurrence: It is used to specify for which occurrence of a match we are going to search. If we will not specify this parameter, by default, it is 1.

return_option: It is used to specify which type of position of the matched substring will return. If its value is 0, it means the function returns the position of the matched substring's first character. If its value is 1, it will return the position following the matched substring. If we will not specify this parameter, by default, it is 1.

match_type: It is a string that uses the following possible characters to perform matching.

c: It represents a case-sensitive matching.
i: It represents a case-insensitive matching.
m: It represents a multiple-line mode that allows line terminators within the string. By default, this function matches line terminators at the start and end of the string.
n: It is used to modify the . (dot) character to match line terminators.
u: It represents Unix-only line endings.

Let us understand it with various examples.

Example

This statement returns the starting index of substring 'BCA' within the expression. If we execute the statement, it will return 1, which is the given string's first index. It is because here, we have not specified any optional parameters.

mysql> SELECT REGEXP_INSTR ('BCA MCA PGDCA BBA MBA BCA', 'BCA') AS start_index;

See the below output:

In this statement, we locate the appearance of the pattern string 'BCA' to the second time by modifying the query with optional parameters position and occurrence.

mysql> SELECT REGEXP_INSTR ('BCA MCA PGDCA BBA MBA BCA', 'BCA', 1, 2, 0) AS BCA_index_2;

See the below output:

In this statement, we have uses the return_option parameter. This parameter is useful when we want to get information that is preceded by some kind of identifier.

mysql> SELECT REGEXP_INSTR ('BCA MCA PGDCA BBA MBA BCA', 'BCA', 1, 1, 1) AS post_index;

See the below output:

Next TopicMySQL regexp_like() Function

← prev next →

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

MySQL regexp_instr() function

MySQL REGEXP_INSTR() Function

Syntax

Parameter Explanation

Example

Related Links:

.Net

Java

Related Links