CODEWITHSUNDEEP
sqlregexpostgresql
Jappa
Jappa

Reputation: 101

PostgreSQL: filter rows by regex

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

Upvotes: 1

Views: 317

Answers (1)

user330315
user330315

Reputation:

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/$

Upvotes: 3

Related Questions