©SQL :: Manager
HomeProgram InformationTechnical detailsFree downloadDonate for a cup of coffee
myway SQL Manager for MySQL and MariaDB, Download on Uptodown
SQL functionMySQL

MySQL REGEXP_LIKE SQL Function - Occurrence of regular expression

REGEXP_LIKESyntax:REGEXP_LIKE(String, Pattern [, Match type])
Return value:INTEGER
Function type:Regular Expressions
Function Description

The REGEXP_LIKE() function returns whether the regular expression "Pattern" was found in "String".

If a match is found, the function returns 1, if no match is found, the result is 0.

If the value of "String" or "Pattern" is NULL, the return value of the REGEXP_LIKE() function is also NULL.


Optionally, the "Match type" can be specified. A string specifying how the match is performed.


TypeDescription
cCase sensitive.
iCase-insensitive match.
mMultiline mode. Line terminators within the string are recognized. The default behavior is to match line terminators only at the beginning and end of "String".
nA point "." is interpreted as a line terminator: Default.
uUnix-only line endings. Only the newline character is recognized as a newline by the ".", ",", "^" and "$" match operators.

If characters are specified within "Match type" that result in conflicting options, the character on the right takes precedence.


This feature is not available in MariaDB.

Examples of the REGEXP_LIKE() function on MySQL 8.0

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

SELECT REGEXP_LIKE('test-abc-abc-abc''Test');
SELECT REGEXP_LIKE(cast('test-abc-abc-abc' as char character set utf8mb4), cast('Test' as char character set utf8mb4));

SELECT REGEXP_LIKE('test-abc-abc-abc''AB');
SELECT REGEXP_LIKE('test-abc-abc-abc''(?i)AB');
SELECT REGEXP_LIKE('test-abc-abc-abc''AB''i');

SELECT REGEXP_LIKE(null'abc');
SELECT REGEXP_LIKE('test-aa-abc'null);

REGEXP_LIKE('test-abc-abc-abc', 'Test')
int(1)
0
REGEXP_LIKE(cast('test-abc-abc-abc' as char character set utf8mb4), cast('Test' as char character set utf8mb4))
int(1)
1
REGEXP_LIKE('test-abc-abc-abc', 'AB')
int(1)
0
REGEXP_LIKE('test-abc-abc-abc', '(?i)AB')
int(1)
1
REGEXP_LIKE('test-abc-abc-abc', 'AB', 'i')
int(1)
1
REGEXP_LIKE(null, 'abc')
int(1)
NULL
REGEXP_LIKE('test-aa-abc', null)
int(1)
NULL
The examples were created with the MyWAY SQL manager: Download

How to use the REGEXP_LIKE() function in MySQL databases

In MySQL, the REGEXP_LIKE() function is used to perform pattern matching with regular expressions. Strings matching a specific pattern can be searched by using regular expressions. If there is a match, the function returns 1, if there is no match, the result is 0. The function provides a flexible way to perform pattern matching using regular expressions and can be used in a number of ways when looking for specific patterns or input must be validated against regular expressions.

The function does not exist in MariaDB.
RANDOM_BYTESREGEXPREGEXP_INSTRREGEXP_LIKEREGEXP_REPLACEREGEXP_SUBSTRREPEAT

Further MySQL und MariaDB SQL Regular Expressions

REGEXPString [NOT] REGEXP Pattern
More about REGEXP Function

REGEXP_INSTRREGEXP_INSTR(String, Pattern [, Position] [, Occurrence] [, Return option] [, Match type])
More about REGEXP_INSTR Function

REGEXP_REPLACEREGEXP_REPLACE(String, Pattern, Replace by [, Position] [, Occurrence] [, Match type])
More about REGEXP_REPLACE Function

REGEXP_SUBSTRREGEXP_SUBSTR(String, Pattern [, Position] [, Occurrence] [, Match type])
More about REGEXP_SUBSTR Function

RLIKEString [NOT] RLIKE Pattern
More about RLIKE Function

Numeric functionsString functionsRegular ExpressionsDate and Time functions
Comparison functionsEncryption & CompressionConversion functionsNULL functions
Aggregate functionsWindow functionsJSON functionsGeometric functions
Sequence functionsInformation functionsDynamic ColumnsMiscellaneous functions
More information about the REGEXP_LIKE SQL function: mysql.com
Updated: 2023-09-17ImprintContactTerms & conditionsPrivacy & CookiesUpdatesSitemapFacebookLinkedinTwitterStatistics©2020-2024 Heino Cunze-Fischer