CODEWITHSUNDEEP
regexpostgresqlsubstringamazon-redshift
user2486308
user2486308

Reputation: 165

Extract a substring from a text string in postgres

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

Upvotes: 1

Views: 9680

Answers (1)

twoflower
twoflower

Reputation: 6830

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.

Upvotes: 5

Related Questions