CODEWITHSUNDEEP
sqlamazon-redshiftaginity
Rishav Dry
Rishav Dry

Reputation: 11

Trying to use regex_replace to extract numbers

I have a column with dma codes. They are either in the following two formats:

a) 100123

b) ="100123"

I am trying to get them all in format a).

I have tried using NULLIF(regexp_replace(column_name, '\D','','g'), '')::numeric

But I am not able to use the proper syntax.

SELECT ds.from_date ,state_map.state_name as state ,dma_region_name ,zip_map.dma_name as dma_name ,regexp_replace(dma_region_id,'','[0-9]') ,postal_code_name ,search_codes_map.is_sourcecode ,search_codes_map.geo ,search_codes_map.tactic ,search_codes_map.engine ,search_codes_map.strategy ,search_codes_map.device ,search_codes_map.campaign_type ,search_codes_map.keyword_type ,search_codes_map.intent_bucket ,search_codes_map.match_type ,search_codes_map.test_indicator ,SUM(ds.clicks) AS clicks ,SUM(ds.impr) AS impressions ,SUM(ds.cost) AS cost ,SUM(ds.phone_calls) AS phone_calls ,SUM(ds.dialogtech_calls) AS dialogtech_calls ,SUM(ds.ebrc_completion) AS ebrc_completion ,0 as smart_phone_leads ,0 as smart_web_leads ,0 as smart_leads ,0 as smart_agent_appointments ,0 as smart_oles ,0 as phone_enrollment ,0 as smart_sales ,0 as smart_paid_sales FROM digital.uhg_mr_is_search ds -- IS campaign-to-source_code mapper LEFT JOIN uhg_part_b.v_is_search_manual_dims_dedupe search_codes_map ON ds.campaign = search_codes_map.campaign_name -- Bring in state acronym -- Yang: Note there's a very small number of cases when we do NOT have state name but do have DMA name in raw data. Didn't map them to state as we do not have a dma id mapper in reftables
LEFT JOIN reftables.us_states state_map ON ds.state_name = state_map.state_name LEFT JOIN reftables.zip_dma_map zip_map ON RIGHT(ds.dma_region_id,3)= zip_map.dma_code -- LEFT JOIN reftables.dcm_dma_map DMA ON dma.dma_code = map.dma_code

WHERE from_date >= '2018-07-01' GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17) GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17

;

Upvotes: 1

Views: 59

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Why not just use replace()?

select replace(replace(dma, '"', ''), '=') as dma_type_a

Upvotes: 1

Related Questions