1

Originally, there were some invalid character varying in the column postal_code. [The valid form is [A-Z][0-9][A-Z]\s[0-9][A-Z][0-9]]. After I executing the following sql command 

CREATE table public.valid_geo 
AS 
SELECT regexp_matches(postal_code, '[A-Z][0-9][A-Z]\s[0-9][A-Z][0-9]') AS postal_code, 
       SUM(total) AS X, 
       (SUM(total)/COUNT(tran_num)) AS Y,  
       COUNT(tran_num) AS Z 
FROM public.raw_data AS postal_code 
GROUP BY postal_code

I got a table with all the value like {"J4D 4K9"} for column [post_code]. Is there a way to convert the value in this column back to 6-digit character varying (Like: J4D4K9[With out the space and '{' '}' ])? Note: I have tried to just access the first element in each array. Then I got the text 'J4D 4K9'. Is there a possible way to convert this text to the character varying without the space in it?

Leo Lin
  • 37
  • 5
  • https://stackoverflow.com/questions/13287274/regexp-matches-better-way-to-get-rid-of-returning-curly-brackets – giorgiga Jan 18 '18 at 21:50
  • Possible duplicate of [regexp\_matches better way to get rid of returning curly brackets](https://stackoverflow.com/questions/13287274/regexp-matches-better-way-to-get-rid-of-returning-curly-brackets) – giorgiga Jan 18 '18 at 21:51
  • Thanks, I figured it out. Just using the replace function to get rid of the space https://stackoverflow.com/questions/10432086/remove-all-spaces-from-a-string-in-sql-server – Leo Lin Jan 18 '18 at 22:17

0 Answers0