I have been using this query in Postgres 9.1 in order to remove ''
from existing data.
Below is a sample of the data before running regexp_replace()
:
6''
6''
6''
20''
12''
18''
20''
8''
10''
''
''
Upon running:
select REGEXP_REPLACE(regexp_replace, $$^'*$$, '' , 'g')
from temp_4 order by id;
I receive this clean output:
6
6
6
20
12
18
20
8
10
( ) <- stand-in for " "
( )
however when attempt to then write these results into a table, say using:
select * into table_3 from (select REGEXP_REPLACE(tbl, $$^"''*,*$$, '' , 'g') from temp_2 order by id) as temp_3;
I receive
6
6
6
20
12
18
20
8
10
''
''
Where ''
has been removed from all values, except in the case it was the only value present. I have attempted to whitelist using [\w\s*]
all other values instead of blacklist with this regex ^'*
, but in both instances values of ''
. I feel like I'm taking crazy pills.
How do I write my table where I can replace ''
with 0
or null values?
Also, is my syntax terribly wrong in my attempts to write this data to other tables? Is there a better way to do this?
Best Answer
Your
regexp_replace()
statement is invalid. It would work like this:Your
SELECT INTO
statement is invalid. It would look like this:But I would use neither.
SELECT INTO
is discouraged. Only supported for historical reasons. UseCREATE TABLE AS
instead, which is the SQL standard way. Per documentation:Bold emphasis mine.
For the presented examples,
rtrim()
is much simpler and faster: