mercredi 18 mars 2009

Comparaison de texte PostgreSQL

Publié par Infinite Loop, à 19 h 38 1 commentaire

La base de données PostgreSQL offre plusieurs opérateurs utiles pour effectuer des comparaisons.

Opérateur =
Pour comparer de façon exacte deux chaînes de caractères :

SELECT * FROM table WHERE field = 'code18'

LIKE
Le mot réservé LIKE est standard dans le monde des bases de données. Il permet de vérifier si le texte comparé respecte partiellement un pattern :

commence par : WHERE title LIKE 'code%'
se termine par : WHERE title LIKE '%18'
contient : WHERE title LIKE '%code%'

ILIKE
Semblable à LIKE, ILIKE permet de faire la même comparaison mais en ne tenant pas compte de la casse (case insensitive) :

WHERE title LIKE '%CoDe%' -- ne trouvera pas "code18"
WHERE title ILIKE '%CoDe%' -- le trouvera

SIMILAR TO
Permet d'utiliser un pattern qui combine, comme pour LIKE, les wildcards _ (n'importe quel caractère unique) et % (n'importe quoi) :

WHERE encoding SIMILAR TO '[a-zA-Z0-9]%'
WHERE encoding SIMILAR TO '%utf-(8|16)%'

Ici, '%utf-*(8|16)%' est équivalent à '%utf-%(8|16)%'.

Expression régulière POSIX
Ou encore utiliser des expressions régulières. Un opérateur équivalent existe au pour case insensitive (~*) :

WHERE song_title ~ '^[0-9]+'

Résultats :
99 Luftballoons - Nena
3ème avertissement - Miriodor
100,000 People - Philip Glass

Accent Insensitive ?
Malheureusement, malgré la version 8.3 de PostgreSQL, il n'y a toujours aucune trace d'une recherche insensible aux accents. On peut certainement partenir à quelque chose en convertissant le texte avec to_ascii() mais avec certains encodages comme UTF-8, ce n'est pas supporté correctement. Un workaroung possible serait de créer une fonction qui remplace les accents pour pouvoir effectuer la comparaison. Aujourd'hui, Thom Brown a proposé sur la liste de diffusion de Postgre une fonction appelée unaccent_string :

CREATE OR REPLACE FUNCTION unaccent_string(text)
RETURNS text AS $$
my ($input_string) = @_;
$input_string =~ s/[âãäåaaa]/a;
$input_string =~ s/[ÁÂÃÄÅAAA]/A;
$input_string =~ s/[èééêëeeeee]/e;
$input_string =~ s/[EEEEE]/E;
$input_string =~ s/[ìíîïìiii]/i;
$input_string =~ s/[ÌÍÎÏÌIII]/I;
$input_string =~ s/[óôõöooo]/o;
$input_string =~ s/[ÒÓÔÕÖOOO]/O;
$input_string =~ s/[ùúûüuuuu]/u;
$input_string =~ s/[ÙÚÛÜUUUU]/U;
return $input_string;
$$ LANGUAGE plperl;

Appel :
WHERE unaccent_string(address) ILIKE unaccent_string('Montréal')

Finalement, une piste de solution viendra peut-être éventuellement dans une version ultérieure avec Text Search Configuration.