Remove hex character from Postgres data

user_4971515 0
Viewed 1342

I have a Postgres database bg that uses WIN1252 encoding. To get around UTF8 conversion issues, I want to remove values like 0x9D from my data. How would I remove these values from a column named comments in bg.bg."DEV"?

Based on this question, I've tried:

UPDATE bg.bg."DEV"
SET "comments" = REPLACE(comments, X'9D','') 
WHERE "comments" like '%'||x'9D'||'%';

However this throws:

ERROR:  "%" is not a valid binary digit
LINE 3: WHERE "comments" like '%'||x'9D'||'%';
                              ^
postgresql database
2 Answers

In the end I was able to use the following:

UPDATE tablename SET field=replace(field, chr(x'9D'::int), '')
WHERE strpos(field,chr(x'9D'::int))>0;

As per this question on dba.stackexchange

user_49715150

If you are looking to match 0x9D in the comments column. The where clause should be 

WHERE "comments" like '%0x9D%'
user_22165820
Related