compare values using regex sql

Question

Does anyone knows how to say if val1 = X/state/Amercia/LA/ or X/state/ the val2 = 'LA'.

code

 when TRIM(val1) like '^X*/state/' or TRIM(val1) like '^X*/LA/' then 'LA'

Thank you

Please provide sample data, desired results, a clear explanation, and a database tag. — Gordon Linoff
– Gordon Linoff, Commented Jul 15, 2020 at 13:35
The like operator does not accept any regular expression, but requires a PostgreSQL dialect. You can look it up here. Your best option is the official documentation on the subject. — Sander
– Sander, Commented Jul 19, 2020 at 4:42

Mike Organek · Accepted Answer · 2020-07-15 13:49:24Z

1

Given these results (please note I preserved the spelling of 'Amercia'):

select trim('X/state/Amercia/LA/') ~ '^X/state/($|Amercia/LA/$)';
 ?column?
----------
 t
(1 row)

select trim('X/state/Amercia/LA/') ~ '^X/state/($|Amercia/LA/$)';
 ?column?
----------
 t
(1 row)

select trim('X/state/') ~ '^X/state/($|Amercia/LA/$)';
 ?column?
----------
 t
(1 row)

select trim('X/state/SomethingElse') ~ '^X/state/($|Amercia/LA/$)';
 ?column?
----------
 f
(1 row)

select trim('Xother') ~ '^X/state/($|Amercia/LA/$)';
 ?column? 
----------
 f
(1 row)

select trim('X/state/Amercia/VA') ~ '^X/state/($|Amercia/LA/$)';
 ?column? 
----------
 f
(1 row)

Your when should be:

  when trim(val1) ~ '^X/state/($|Amercia/LA/$)' then 'LA'

answered Jul 15, 2020 at 13:49

Mike Organek

12.6k3 gold badges15 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

compare values using regex sql

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