Search
Greetings,
I store text data in windows-1251 encoding in my pgSQL database
and when I execute SQL command like
SELECT title, firstname, lastname, description FROM stuff
WHERE title LIKE '%<keyword>%' AND firtsname LIKE '%<keyword>%'
AND lastname LIKE '%<keyword>%' AND description LIKE '%<keyword>%'
the response comes really quick, but if I want case independent
search, I should convert, using my own PHP3 function, both search
field and keyword to low case just becouse PostgreSQL by itself
doesn't know how to convert characters in windows-1251 encoding
to low case. And response time grows from 0:07 to 1:10!
Is there any solution? May be stored procedures (that doesn't
require to recompile pgSQL)? Thank you in advance!!!
P.S.: as far as I know there is no locale for windows-1251 and,
unfortunately, I can't store data in koi8-r whose locale present
in almost all UNIX'es.
Mentioned above function:
function lower($str)
{
$length = strlen($str);
for ($i = 0; $i < $length; ++$i)
{
$sym = ord($str[$i]);
if (($sym > 34 && $sym < 60) || ($sym > 191 && $sym < 224))
$str[$i] = chr($sym + 32);
}
return $str;
}
--
BEST WISHES, Dmitry
Webmaster, DeltaCube Studio
http://www.deltacube.com/
Hi,
For case insensitive you must use the regexp search as follow :
SELECT title, firstname, lastname, description FROM stuff
WHERE title *~ '.*<keyword>.*' AND firtsname *~ '.*<keyword>.*';
P.S.: as far as I know there is no locale for windows-1251 and,
unfortunately, I can't store data in koi8-r whose locale present
in almost all UNIX'es.
I don't know about windows-1251. Perhaps this can't help you. But if
you have japanese in you database, you can proceed a search on it so
why not windows-1251 ?
A+
Gilles
At 03:43 +0300 on 01/08/1999, Gilles Darold wrote:
I don't know about windows-1251. Perhaps this can't help you. But if
you have japanese in you database, you can proceed a search on it so
why not windows-1251 ?
Because the backend has to know that the lowercase for char NNN in this
codepage is MMM. This is different from one locale to the next. If it were
windows-1255 (Hebrew), there would be no lowercase at all for any character
above 224. So of course it's different than Japanese.
There can be two solutions to this problem:
1) Write a function using SPI, install it on the backend, and use
it for the comparison.
2) Create the new locale, or at least the LC_CTYPE part of the locale,
on the unix you are using.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
On Sat, 31 Jul 1999, Dmitry Popov wrote:
I store text data in windows-1251 encoding in my pgSQL database
and when I execute SQL command likeSELECT title, firstname, lastname, description FROM stuff
WHERE title LIKE '%<keyword>%' AND firtsname LIKE '%<keyword>%'
AND lastname LIKE '%<keyword>%' AND description LIKE '%<keyword>%'the response comes really quick, but if I want case independent
search, I should convert, using my own PHP3 function, both search
field and keyword to low case just becouse PostgreSQL by itself
doesn't know how to convert characters in windows-1251 encoding
to low case. And response time grows from 0:07 to 1:10!Is there any solution? May be stored procedures (that doesn't
require to recompile pgSQL)? Thank you in advance!!!
P.S.: as far as I know there is no locale for windows-1251 and,
unfortunately, I can't store data in koi8-r whose locale present
in almost all UNIX'es.
Incorrect solution: install win1251 locale. Once I saw a reference to
such beast in Russian Apache mailing list. Search through mail archive,
look into Apache-Rus FAQs...
Correct solution: use koi8-r locale and koi8<=>win1251 on-the-fly
conversion. Postgres starting from 6.5 can do on the fly conversion between
many Cyrillic locales. Look into doc/README.locale.
--
BEST WISHES, Dmitry
Webmaster, DeltaCube Studio
http://www.deltacube.com/
Oleg.
----
Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net
Programmers don't die, they just GOSUB without RETURN.