On Tue, Jul 21, 2009 at 6:35 PM, Sam Mason<[email protected]> wrote:
> On Tue, Jul 21, 2009 at 09:37:04AM +0200, Daniel Verite wrote:
>> >I'd love to fix them. But if I do a search for
>> >SELECT * FROM xyz WHERE col like '%0x80%'
>> >
>> >it doesn't work. How should I search for these characters?
>>
>> In 8.2, try: WHERE strpos(col, E'\x80') > 0
>>
>> Note that this may find valid data as well, because the error you get
>> is when 0x80 is the first byte of a character in UTF8; when it's at
>> another position, you don't want to change it.
>
> There are various regexs around to check for valid UTF-8 encoding; one
> appears to be:
>
> http://keithdevens.com/weblog/archive/2004/Jun/29/UTF-8.regex
>
> One translation into PG would be:
>
> WHERE NOT col ~ ( '^('||
> $$[\09\0A\0D\x20-\x7E]|$$|| -- ASCII
> $$[\xC2-\xDF][\x80-\xBF]|$$|| -- non-overlong 2-byte
> $$\xE0[\xA0-\xBF][\x80-\xBF]|$$|| -- excluding overlongs
> $$[\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}|$$|| -- straight 3-byte
> $$\xED[\x80-\x9F][\x80-\xBF]|$$|| -- excluding surrogates
> $$\xF0[\x90-\xBF][\x80-\xBF]{2}|$$|| -- planes 1-3
> $$[\xF1-\xF3][\x80-\xBF]{3}|$$|| -- planes 4-15
> $$\xF4[\x80-\x8F][\x80-\xBF]{2}$$|| -- plane 16
> '*)$' );
>
> This seems to do the right thing for me in an SQL_ASCII database.
>
I tried this. Get an error.
mypg=# select * from interesting WHERE NOT description ~ ( '^('||
mypg(# $$[\09\0A\0D\x20-\x7E]|$$|| -- ASCII
mypg(# $$[\xC2-\xDF][\x80-\xBF]|$$|| -- non-overlong 2-byte
mypg(# $$\xE0[\xA0-\xBF][\x80-\xBF]|$$|| -- excluding overlongs
mypg(# $$[\xE1-\xEC\xEE\xEF][\x80-\xBF]{2}|$$|| -- straight 3-byte
mypg(# $$\xED[\x80-\x9F][\x80-\xBF]|$$|| -- excluding surrogates
mypg(# $$\xF0[\x90-\xBF][\x80-\xBF]{2}|$$|| -- planes 1-3
mypg(# $$[\xF1-\xF3][\x80-\xBF]{3}|$$|| -- planes 4-15
mypg(# $$\xF4[\x80-\x8F][\x80-\xBF]{2}$$|| -- plane 16
mypg(# '*)$' )
mypg-#
mypg-# ;
ERROR: invalid regular expression: quantifier operand invalid
--
Sent via pgsql-general mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
