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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
