-1

I am trying to create a regex pattern that follows the pattern Quote + 6 digits + Quote i.e.

  • "123456"
  • "789456"
  • "456456"
REGEXP_LIKE(String, '\"\^[0-9]{6}$\"\') as reg

Any idea what i'm doing wrong? My end goal is to simply return the % where this equals FALSE

urdearboy
  • 13,493
  • 3
  • 17
  • 44
  • 1
    Your `^` and `$` boundaries are misplaced. `^` matches the beginning of the input and `$` matches the end. It's not possible that `"^` matches given `^` will never be the beginning of input. – plalx May 20 '20 at 22:01
  • 1
    thanks for the fast re's - just now trying to learn regex. Mighty confusing at first – urdearboy May 20 '20 at 22:03

2 Answers2

1

I think you want:

where regexp_like(string, '^"[0-9]{6}"$')
---------------------------^ begins with
----------------------------^ "
-----------------------------^ digit
----------------------------------^ 6 of'em
-------------------------------------^ "
--------------------------------------^ end of string
Gordon Linoff
  • 1,122,135
  • 50
  • 484
  • 624
0

I would write this as:

where regexp_like(string, '^"\d{6}"$')

Rationale:

  • you don't need to escape the double quote - the regex engine understands it just as what it is: a literal double quote

  • the beginning and end of the sring (resp '^' and '$') must be placed at the very beginning and end of the pattern

  • '[0-9]' can be shortened as '\d'

GMB
  • 188,822
  • 23
  • 52
  • 100
  • Does the `d` just stand for digits here? – urdearboy May 20 '20 at 22:04
  • 2
    It's basically an alias for `[0-9]`. – plalx May 20 '20 at 22:04
  • @urdearboy: `\d` means "match a digit character, i.e. 0-9". It's equivalent to the POSIX regular expression class `[[:digit:]]`. You can see how the regex `^"\d{6}"$` is evaluated by looking at the explanation pane at [regex101](https://regex101.com/r/x7zRD7/1) – Bob Jarvis - Reinstate Monica May 20 '20 at 22:14
  • Good to know. Thanks for explaining how this works. Being new, I like the [0-9] approach so it's clear what is going on although I can see how the `d` approach is prob used by more regex seasoned users – urdearboy May 20 '20 at 22:31