CODEWITHSUNDEEP
databasepostgresql
Jesse Reilly
Jesse Reilly

Reputation: 317

Remove hex character from Postgres data

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'||'%';
                              ^

Upvotes: 3

Views: 2194

Answers (2)

Jesse Reilly
Jesse Reilly

Reputation: 317

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

Upvotes: 3

Uttam
Uttam

Reputation: 83

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

WHERE "comments" like '%0x9D%'

Upvotes: 0

Related Questions