Substring Extract

Database

Substring Extract

I have a reallt long string that I am trying to substring a specific piece of data for.

The column looks like this "http://www.sears.com/lawn-garden-snow-removal-equipment-snow-plows/b-1101194?sid=ISx20070515x00001d&..."

I built a table with the query below to test:

CREATE VOLATILE TABLE test_str, NO LOG AS

(

    SELECT

CAST('http://www.sears.com/lawn-garden-snow-removal-equipment-snow-plows/b-1101194?sid=ISx20070515x00001d&psid=18x813487&knshCrid={AdId}&k_clickID=_kenshoo_click' AS VARCHAR(200)) AS long_str

) WITH DATA PRIMARY INDEX(long_str) ON COMMIT PRESERVE ROWS;

I want to extract from that column "18x813487" which is right after the "psid=" and before the next & symbol.

I wrote the below to do it

SELECT SUBSTRING(long_str from POSITION('psid=' IN long_str),POSITION('&' IN SUBSTRING(long_str from POSITION('psid=' IN long_str),LENGTH(long_str)-POSITION('psid=' IN long_str)-1))-1) AS psid

FROM test_str;

But I get an error that says "SELECT Failed. [3706] Syntax error:  Data type "long_str" does not match a Define Type name

I could really use some expert help on this one if anyone has any suggestions

Tags (1)
2 REPLIES

Re: Substring Extract

Nevermind, I fixed it:>

SELECT SUBSTRING(long_str from POSITION('psid=' IN long_str),POSITION('&' IN SUBSTRING(long_str from POSITION('psid=' IN long_str),CHARACTER_LENGTH(long_str)-POSITION('psid=' IN long_str)-1))-1) AS psid

FROM test_str;

Junior Contributor

Re: Substring Extract

You don't need to specify the length when you want to get all chatacters until the end of the string, so this can be further simplified:

SELECT
SUBSTRING(long_str FROM POSITION('psid=' IN long_str)
FOR POSITION('&' IN SUBSTRING(long_str FROM POSITION('psid=' IN long_str)))-1) AS psid
FROM test_str;

If you're on 14 better use a regular expression:

SELECT
REGEXP_SUBSTR(long_str, '(?<=psid=).+?(?=&)',1,1,'i'), -- excluding "psid="
REGEXP_SUBSTR(long_str, 'psid=.+?(?=&)',1,1,'i') -- including "psid="
FROM test_str