CODEWITHSUNDEEP
sqlpostgresql
Programmer120
Programmer120

Reputation: 2592

How to find position of multiple sub string in PostgreSQL?

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.

Upvotes: 1

Views: 2207

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17157

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?

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

Thanks @Abelisto for input

Upvotes: 3

Related Questions