CODEWITHSUNDEEP
sqlregexamazon-redshift
izzy84
izzy84

Reputation: 259

REGEX_SUBSTR Redshift

I need to extract the day and month from a campaign code, but my query only returns NULL values. The day and month always follow the format of 'mmmdd' (e.g., aug23, sep12, oct09) although the campaign values vary in total length.

Sample Query:

SELECT Campaign,
       Year,
       REGEXP_SUBSTR(campaign, '[a-z]{3}\d{2}') AS Month_Day

FROM GA_CAMPAIGN

Sample Table:

Campaign                                           | Year | Month_Day
TL-17-push-general-sep15septemberhighlights-0-0-0- | 17   | NULL
FA-17-stoke-aug09augusthighlights-0-0-0-0-0        | 17   | NULL

Desired Output:

Campaign                                           | Year | Month_Day
TL-17-push-general-sep15septemberhighlights-0-0-0- | 17   | sep15
FA-17-stoke-aug09augusthighlights-0-0-0-0-0        | 17   | aug09

Thank you in advance!

Upvotes: 0

Views: 150

Answers (1)

Joe Harris
Joe Harris

Reputation: 14045

The \d is not working as you expect. Try using [0-9] instead.

WITH sample AS (
          SELECT 'TL-17-push-general-sep15septemberhighlights-0-0-0' campaign
UNION ALL SELECT 'FA-17-stoke-aug09augusthighlights-0-0-0-0-0'       campaign
)
SELECT campaign,
       REGEXP_SUBSTR(campaign, '[a-z]{3}[0-9]{2}') AS Month_Day
FROM sample
;
--                     campaign                      | month_day
-----------------------------------------------------+-----------
-- TL-17-push-general-sep15septemberhighlights-0-0-0 | sep15
-- FA-17-stoke-aug09augusthighlights-0-0-0-0-0       | aug09

Upvotes: 1

Related Questions