how to regex in SQL Redshift

Question

I have the following url:

pro.somecompany.com/#/aquisitionintelligence/competitiveanalysis/overview/website-performance/hospital.org/*/484/3m?webSource=Desktop

I want to the aquisitionintelligence part and the competitiveanalysis part

Also, sometimes I have links like so /#/insights/reports?types=SC

How do I get only the reports part?

any idea how to do it in SQL?

Please edit your question to provide more sample inputs, and what you are seeking as the output for each of those inputs. — John Rotenstein
– John Rotenstein, Commented Oct 13, 2020 at 10:40

Gordon Linoff · Accepted Answer · 2020-10-13 10:41:07Z

1

You can use regexp_substr() for the general problem of a complex delimiter.

For the third part for instance:

select regexp_substr(col, '[^/?]+', 1, 3)

If you want just the third part using a single delimiter, use split_part():

select split_part(col, '/', 3)

answered Oct 13, 2020 at 10:41

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

nimi1234 Over a year ago

This works beautifully! Can you explain the special characters and structure? or refer me to a good source?

Gordon Linoff Over a year ago

@nimi1234 . . . I referenced the documentation on the functions. The "special characters" are the delimiters. In the first case, they are characters not allowed in the returned value. In the second, it is just the delimiter.

Collectives™ on Stack Overflow

how to regex in SQL Redshift

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related