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
;
