How to query for multiple sub strings with position in SQL?

Question

I have the following query:

select position( '/s/' in col)
from table

This works.

Now, I want to be able to find the position of /s/ or /d/ or /c/

I tried:

select position( '[/s/][/c/][/d/]' in col)
from table

But it returns a wrong value.

Example:

select position( '/s/' in '/s/')

returns 1.

select position( '[/s/][/d/][/c/]' in '/s/')

returns 0

How can I solve this?

Edit:

It can contains only one of /s/ or /d/ or /c/. The string will not contain /s/d/ etc...
There can't be more the one occurrences of the matched sub-string. The string won't contain '/s/..../s/'

In simple words - I'm looking for the first occurrence of /s/ or /d/ or /c/ don't worry about the edge cases.

What if col contains both /s/ and /d/? Does it matter which position to return if there are multiple matches? — Ruud Helderman
– Ruud Helderman, Commented Aug 19, 2018 at 15:16
@RuudHelderman it won't. It can contains only one of them. It's indenture that it contains only one occurrence of such. — Programmer120
– Programmer120, Commented Aug 19, 2018 at 15:16
Are you sure you can use patterns there? If yes, try position('/s/|/d/|/c/' in '/s/'). — sticky bit
– sticky bit, Commented Aug 19, 2018 at 15:34
@stickybit doesn't work. I need a way to make it work other than write different position query for each sub-sring :( — Programmer120
– Programmer120, Commented Aug 19, 2018 at 15:37

Ruud Helderman · Accepted Answer · 2018-08-19 15:41:06Z

2

This might do the trick:

SELECT MAX(patlist.pattern) FROM (VALUES (POSITION('/s/' IN col)), (POSITION('/d/' IN col)), (POSITION('/c/' IN col))) AS patlist(pattern)

Wrapped inside a query:

SELECT (SELECT MAX(patlist.pattern) FROM (VALUES (POSITION('/s/' IN col)), (POSITION('/d/' IN col)), (POSITION('/c/' IN col))) AS patlist(pattern))
FROM MyTable

Disclaimer: in absence of a prestodb instance, I could only test it on an alternate database engine. It may or may not work on prestodb.

answered Aug 19, 2018 at 15:41

Ruud Helderman

11.1k1 gold badge28 silver badges49 bronze badges

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

Comments

sticky bit · Accepted Answer · 2018-08-19 15:48:17Z

1

Since you wrote in the comments, that only one of the substrings will be present and position() returning 0, if the substring isn't found, you just can check the sum of multiple position()s.

SELECT position('/s/' in col)
       +
       position('/d/' in col)
       +
       position('/c/' in col)
       FROM table;

Or even nicer, since Presto has a greatest() function, you could do:

SELECT greatest(position('/s/' in col),
                position('/d/' in col),
                position('/c/' in col))
       FROM table;

Like that you get the greatest position.

edited Aug 19, 2018 at 15:48

answered Aug 19, 2018 at 15:43

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

1 Comment

Programmer120 Over a year ago

Yeah I thought of this solution but wanted to know if there is a way to avoid the multiple position queries.. I have like 15 of theses :(

Collectives™ on Stack Overflow

How to query for multiple sub strings with position in SQL?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related