Extract a substring from a text string in postgres

Question

I am trying to extract a substring from a text string in postgresql. The column name of the text string is URL and I am using the subtring function to define a regex that will return only the portion that I want:

SELECT
substring('user_latitude=.*user_' from URL)
FROM my_table
limit 10

When I execute this query, I am greeted with the following response:

SQL Error [XX000]: ERROR: Not implemented Detail:

error: Not implemented code: 1001 context: 'false' - Function substring(text,text) not implemented - use REGEXP_SUBSTR instead query: 258128 location: cg_expr.cpp:4265 process: padbmaster [pid=52019]

org.postgresql.util.PSQLException: ERROR: Not implemented Detail:

error: Not implemented code: 1001 context: 'false' - Function substring(text,text) not implemented - use >REGEXP_SUBSTR instead query: 258128 location: cg_expr.cpp:4265 process: padbmaster [pid=52019]

However, REGEXP_SUBSTR does not appear to be a native function, and does not work.

Is there a way to extract a value from a string in POSTGRES?

Thanks

twoflower · Accepted Answer · 2015-09-09 07:46:32Z

5

Since the Amazon Redshift you are running is built on Postgres 8.0.2 and furthermore lot of the Postgres functions is not supported, it is good to take a look at the documentation to see what actually is available.

The SUBSTRING function has a signature different to how you have used it, it should be something like this:

select
substring(URL from 14)
from my_table
limit 10

This would yield the portion of URL from the 14th character to the end.

Alternatively, if not even this version of SUBSTRING is supported, try using the suggested REGEXP_SUBSTR:

select
regexp_substr(URL, '=.*')
from my_table
limit 10

This would return the portion of URL from '=' onward.

edited Sep 9, 2015 at 7:46

answered Sep 8, 2015 at 21:11

twoflower

6,8283 gold badges36 silver badges45 bronze badges

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

Collectives™ on Stack Overflow

Extract a substring from a text string in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related