CODEWITHSUNDEEP
sqlpostgresql
dsp
dsp

Reputation: 171

compare values using regex sql

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

Upvotes: 0

Views: 134

Answers (1)

Mike Organek
Mike Organek

Reputation: 12494

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'

Upvotes: 1

Related Questions