How to find position of multiple sub string in PostgreSQL?

Question

I have a string of this type:

URL/X/help?

X stands for chars like: a,b,c etc... Only one chars in that place. The length of URL is unknown it can be: www.website.com , www.website.co.uk , www.website.info etc...

I want to find out the position of /X/ in the string but only if X is: s,b,t

/X/ - appear once and only once in the string.

Something like:

select position ('/s/' or '/b/' or '/t/' in URL)

however this is not PostgreSQL syntax.

Is there a way to write this query except doing :

select position ('/s/' in URL) + 
select position ('/b/' in URL) +
select position ('/t/' in URL)

EDIT: There are no complex cases. No need to worry about /s/s or /b//s/ or /s/g/ or /s/..../s/ etc... Just the simple case I presented. It also guaranteed that /CHAR/ will appear exactly once but the char is unknown.

@LaurenzAlbe isn't there a way to use reg expressions to handle this? — Programmer120
– Programmer120, Commented Aug 20, 2018 at 6:39
@Programmer120 what's your sample input and expected output, please edit question and fill this in. Include the most complicated combination of data you can think of to this sample so that it covers everything you want. — Kamil Gosciminski
– Kamil Gosciminski, Commented Aug 20, 2018 at 6:43
@Programmer120 Even if there is a way to do this with regular expressions (I can't think of one), it would certainly be slower than your idea. Regular expressions use a lot of CPU. — Laurenz Albe
– Laurenz Albe, Commented Aug 20, 2018 at 6:48
@KamilG. There is no complected case. This is my whole example. /CHAR/ appear once and only once. There can't be a mix. — Programmer120
– Programmer120, Commented Aug 20, 2018 at 6:52

Kamil Gosciminski · Accepted Answer · 2018-08-20 08:13:23Z

3

Since you wanted to know if there's a way to do this using regular expression, here's one:

select 
  coalesce(nullif(length(regexp_replace(url, '\/[sbt]\/.*', '')), length(url))+1, 0) as pos
from ( select 'www.webexample.com/s/help?' as url union all
       select 'www.webexample.com/d/help?' as url ) t;

Outputs:

| pos |
+-----+
| 19  |
| 0   |

How it works?

Using regexp_replace find & replace string part starting from /s/, /b/ or /t/ to the end of string with blank character ''
Calculate length of the result to see the position of last character before /
Apply nullif function which returns NULL if length of an url after string replacement equals length of the original url
Add +1 to get the position of slash right before any of characters from class: [sbt]
Apply coalesce function which translates NULL values to 0

Thanks @Abelisto for input

edited Aug 20, 2018 at 8:13

answered Aug 20, 2018 at 7:16

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

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

6 Comments

Programmer120 Over a year ago

Works greate except once case... when the [sbt] doesn't exist for example: select length(regexp_replace(url, '\/[sbt]\/.*', ''))+1 from ( select 'www.webexample.com/d/help?' as url ) t it should give 0 in this case. Other than that works awesome

Abelisto Over a year ago

@Programmer120 nullif(length(regexp_replace(url, '\/[sbt]\/.*', '')), length(url))+1 will return NULL if substring not found.

Kamil Gosciminski Over a year ago

Adjusted my answer with a possible solution.

Abelisto Over a year ago

@KamilG. Why not just position('|!|' in regexp_replace(url, '\/[sbt]\/.*', '|!|')) ?

Kamil Gosciminski Over a year ago

@Abelisto thanks for pointing that out. I overcomplicated things. nullif + coalesce will do the work.

|

Collectives™ on Stack Overflow

How to find position of multiple sub string in PostgreSQL?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related