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

Question

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

Expected_output

Thank you,

Hello, please why are noting negatively my question? tell me if im not clear. thank you . (its my first time) — hamam
– hamam, Commented Aug 15, 2020 at 16:41
You should add full code / query what you have tried so far with problem statement. Also it is good practice to add sample data and desired output — Akhilesh Mishra
– Akhilesh Mishra, Commented Aug 15, 2020 at 17:10
Have you taken the tour? Have you visited the Help Center? Have you read this: How do I ask a good question? — Abra
– Abra, Commented Aug 15, 2020 at 18:06
@AkhileshMishra thank you for your response, i added a description to understand my problem. Please have a look on my original post . Thank you — hamam
– hamam, Commented Aug 16, 2020 at 21:36

Akhilesh Mishra · Accepted Answer · 2020-08-17 05:12:06Z

1

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

answered Aug 17, 2020 at 5:12

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

2 Comments

Akhilesh Mishra Over a year ago

@Matt It will comply the requirement mentioned in the comment check here

Matt Over a year ago

My bad, got confused with the output, yeh your solution is much cleaner!

Matt · Accepted Answer · 2020-08-16 23:12:39Z

0

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

edited Aug 16, 2020 at 23:12

answered Aug 16, 2020 at 21:41

Matt

15.1k28 gold badges109 silver badges161 bronze badges

2 Comments

hamam Over a year ago

Thank you for your comment. imagine that i have this case temp233/state/, col2 will contains cp1 in this case and its not i want, please don"t hesitate if you have another idea.

Matt Over a year ago

@hamam Please see solution 3

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related