CODEWITHSUNDEEP
sqlregexpostgresql
hamam
hamam

Reputation: 47

Match exact string with fixed start and end using regex in postgresql

I'm writing an regex to create col2 and fill it with cp1 if respects condition else "not" if not.

The condition is if f1== name***/state/ then col2 will contains cp1 else col2 will contains "NOT".

I wrote the code below

when trim(f1) ~  '^name[^/]*/state/' then 'cp1' 
else "not" as col2

I'm getting bad results as shown in the attached images.

Does anyone knows how to solve it please?

Actual_output

Actual_output

Expected_output

Expected_output

Thank you,

Upvotes: 1

Views: 875

Answers (2)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

Your regex should be ^name.*state\/$'.

It will check whether string is starting with name and ending with state/.

so you final query will be:

select
f1,
case
when trim(f1) ~  '^name.*state\/$' then 'cp1' 
else 'not' end as col2
from table_

DEMO

Upvotes: 1

Matt
Matt

Reputation: 15061

I would make it simpler and use the RIGHT function.

SELECT f1,
CASE WHEN RIGHT(f1, 6) = 'state/' THEN 'cp1' ELSE 'NOT' END col2
FROM yourtable

Output

f1                  col2
name123/state/LA    NOT
name123/state/LA/X1 NOT
name233/state/      cp1
name1/state/LA      NOT

SQL Fiddle: http://sqlfiddle.com/#!17/de7bbc/4/0

For a more dynamic solution:

SELECT f1,
CASE WHEN f1 = CONCAT(LEFT(f1, strpos(f1, '/') - 1),'/state/') THEN 'cp1' ELSE 'NOT' END col2
FROM yourtable

SQL Fiddle: http://sqlfiddle.com/#!17/de7bbc/17

EDIT

Solution 3 to take into account temp233/state/ :

SELECT f1,
CASE WHEN f1 = CONCAT('name',split_part(LEFT(f1, strpos(f1, '/') - 1), 'name', 2),'/state/') THEN 'cp1' ELSE 'NOT' END col2
FROM yourtable

SQL Fiddle: http://sqlfiddle.com/#!17/c82a5/1/0

Upvotes: 0

Related Questions