Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've got a postgres 8.4 database with a few mangled characters in one of the columns on a table. For example, the word "don't" gets displayed within psql as:

donâ\u0080\u0099t

I want to be able to dig out these odd characters and replace them with simple ' or " alternatives. However, I'm having a bit of a job getting either ~ or LIKE to be able to pull the correct rows in, let alone be able to replace them.

The database is UTF8 and the client_encoding is also set to UTF8. I've tried selecting \u string and \x strings, but can't seem to get anything reliable. For example, all of the following queries return 0 rows or ERROR: invalid byte sequence for encoding "UTF8": 0x80

select col from tab where col like '%\u0080\u0099%';
select col from tab where col ~'\u0080\u0099';
select col from tab where col like '%\x8099%';
select col from tab where col ~'\x8099';

Any ideas?

share|improve this question
add comment

1 Answer

If what you see is donâ\u0080\u0099t then those are literal backslashes in your table, not escape sequences.

Try to double the backslashes to get literal ones:

select col from tab where col ~~ E'%\\u0080%';

And use the proper E'' syntax for PostgreSQL 8.4 with that.

share|improve this answer
    
Brilliant! This works on the \u0080 characters, I just need to replace the odd characters before that now. psql is printing those on resultsets as It<C3><A2>s, so I'm assuming I'm just looking for unicode chars c3 and a2? Google tells me these characters equate to â, but I'm not sure what escape sequence I need to use to find this - \uc3a2? –  growse Feb 29 '12 at 16:16
    
@growse: It's hard to tell what should be there. It very much depends on how the text was mangled, and there is a myriad of possible ways. Some detective work may be needed. For instance, your example donâ\u0080\u0099t looks very much like it should be don't. A case for replace (col, E'â\\u0080\\u0099',''''). Be sure to view the results in a SELECT statement before applying with UPDATE. –  Erwin Brandstetter Feb 29 '12 at 21:58
    
Also, if you want to insert the character â, you can just insert it as is - you are using UTF-8 as server and client encoding. –  Erwin Brandstetter Feb 29 '12 at 23:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.