![mysql regular expression not there mysql regular expression not there](https://media.cheatography.com/storage/thumb/chimichangasguy_python-regular-expressions.750.jpg)
However, if we start at a different position, the result is different: SELECT
![mysql regular expression not there mysql regular expression not there](https://data.templateroller.com/pdf_docs_html/160/1604/160474/perl-regular-expression-cheat-sheet_print_big.png)
| Occurrence 1 | Occurrence 2 | Occurrence 3 | REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 3) 'Occurrence 3' REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 2) 'Occurrence 2', REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1, 1) 'Occurrence 1', In all cases, we start at position 1: SELECT Here’s an example of using the occurrence argument. REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 6) 'Pos 6' REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2) 'Pos 2', REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 1) 'Pos 1', We started at position 2, which comes after the start of the first occurrence, so the next occurrence is returned instead. Here’s an example of specifying a starting position: SELECT REGEXP_SUBSTR('Cat Cut Cot', 'C.t', 2) Result Here’s an example where there’s no match: SELECT REGEXP_SUBSTR('Lend for land', '^C') Result We’ll get to that soon): SELECT REGEXP_SUBSTR('Lend for land', 'l.nd') Result
![mysql regular expression not there mysql regular expression not there](https://res.cloudinary.com/practicaldev/image/fetch/s--pOgUG_Zd--/c_imagga_scale,f_auto,fl_progressive,h_420,q_auto,w_1000/https://dev-to-uploads.s3.amazonaws.com/i/ml4u890vjan6o19dsved.png)
Example 2 – Multiple Matchesīy default, if there are multiple matches within the string, the first one is returned (although you can specify another occurrence if needed. In this case there’s a match, and the substring is returned. Here’s a basic example: SELECT REGEXP_SUBSTR('Thailand or Cambodia', 'l.nd') Result For example, you can use this argument to specify case-sensitive matching or not. The optional match_type argument allows you to refine the regular expression. If omitted, the first occurrence is used (occurrence 1). The optional occurrence argument allows you to specify which occurrence of the match to search for. The optional pos argument allows you to specify a position within the string to start the search. Where expr is the input string and pat is the regular expression pattern for the substring. The syntax goes like this: REGEXP_SUBSTR(expr, pat]]) the input string doesn’t contain the substring), the result is NULL. In MySQL, the REGEXP_SUBSTR() function returns the substring that matches the given regular expression pattern.