PostgreSQL: filter rows by regex

Question

I want to verify if the string began with name(followed by 3 digits) and ends with state.

My code did this

Actual Output: NOT, NOT, YES, YES, YES, YES, NOT.

Expected Output: NOT, NOT, NOT, NOT, NOT, YES, NOT

CREATE TABLE yourtable
("f1" varchar(19))
;

INSERT INTO yourtable
("f1")
VALUES
  ('name123/state/LA'),
  ('name123/state/LA/X1'),
  ('name1/state/'),
  ('nameabcccc/state/'),
  ('name3444/state/'),
  ('name444/state/'),
  ('name1/state/LA')
 ;

  SELECT f1,
  CASE when trim(f1) ~ '^name[^/]*/state\/$' then 'YES' ELSE 'NOT' END col2
  FROM yourtable

@LaurenzAlbe yes its giving bad results. look at the actual results and the expected ones — Jappa
– Jappa, Commented Aug 20, 2020 at 10:32
You are speaking of "tmp" and "x1", but your pattern has "name" and no "x1". — Laurenz Albe
– Laurenz Albe, Commented Aug 20, 2020 at 10:33

user330315 · Accepted Answer · 2020-08-20 10:38:59Z

3

starts with name (followed by 3 digits) and ends with state.

That's not what your regex does. You are missing a pattern for 3 digits and the / character doesn't need to be escaped.

The following will do what you exepct

trim(f1) ~ '^name[0-9]{3}[^0-9]*/state/$'

So the regex makes sure

the string starts with ^name
is followed by three digits [0-9]{3}
is followed by any other character except a digit [^0-9]*
ends with "/state/" by using /state/$

answered Aug 20, 2020 at 10:38

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: filter rows by regex

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related