Re: converting curly apostrophes to standard apostrophes

On Mon, Aug 15, 2005 at 01:48:00PM -0700, CSN wrote:
> db=>select ascii('’');
>  ascii
> -------
>    226
>
> db=>select id from news where body ilike '%’%';
> (0 rows)
>
> db=>select id from news where body ilike '%' ||
> chr(226) || '%';
> db'>
> db'>^C
> db=>

What's going on with the last query?  The prompt change suggests
that psql is confused with quoting, and the ^C looks like you hit
Control-C to get the regular prompt back.  Did you ever run this
query?  If it produced no rows then you could widen the search.
Example:

SELECT id FROM news WHERE body ~ '[\200-\377]';

You could use the "string from pattern" variant of substring() to
extract characters in a specific range.  If you have PL/Perl then
it would be trivial to extract all of and only the special characters
along with their ASCII codes:

CREATE FUNCTION special_chars(text) RETURNS text AS '
return join(" ", map {"$_:" . ord($_)} $_[0] =~ /[\200-\377]/g);
' LANGUAGE plperl IMMUTABLE STRICT;

SELECT id, special_chars(body) FROM news WHERE body ~ '[\200-\377]';

--
Michael Fuhr