CODEWITHSUNDEEP
mysqlsqlregex
user3344266
user3344266

Reputation: 69

MySQL regex quantifiers not working the way I expect

I'm quite new to regular expressions and I'm not getting what I expect while using regex in MySql. I did investigate these regex expressions at "https://regexr.com/" which is giving me results that are what I expect. The query below returns 3 columns:

  1. one_or_more: I'm expecting to get 6, but I'm getting 0. Doesn't "\s+" mean one ore more?
  2. zero_or_more: I'm expecting 6, but I'm getting 7. If "\s*" means zero or more, shouldn't the match start one character earlier to include the whitespace?
  3. zero_or_once: I'm expecting 6, but I'm getting 7. If "\s?" means one or more, shouldn't the match start one character earlier to include the whitespace?
SELECT
    # 0, 6
    REGEXP_INSTR("Birch Street, Boston, MA 02131, United States", "\s+street") one_or_more,

    # 7, 6
    REGEXP_INSTR("Birch Street, Boston, MA 02131, United States", "\s*street") zero_or_more,

    # 7, 6
    REGEXP_INSTR("Birch Street, Boston, MA 02131, United States", "\s?street") zero_or_once
FROM
    DUAL;

Any helps is appreciated. Thank you. Paul

Upvotes: 0

Views: 59

Answers (1)

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56162

You need to use double \, in this case you'll get the expected results, i.e.:

REGEXP_INSTR("Birch Street, Boston, MA 02131, United States", "\\s+street")

To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters. The MySQL parser interprets one of the backslashes, and the regular expression library interprets the other.

https://dev.mysql.com/doc/refman/8.0/en/regexp.html#regexp-syntax

Upvotes: 1

Related Questions