[PILLOLA] Espressioni regolari nelle query MySql

[gianfry]

Rigrazio innanzitutto Falcao che, con il suo thread 'ricerca parola esatta' mi ha dato lo spunto per preparare questa 'pillola'.
Ai moderatori l'ardua sentenza di stabilire se è degna o meno di entrare nella 'farmacia' del forum.

Effettivamente credo che pochi siano a conoscenza della possibilità di utilizzare le espressioni regolari all'interno delle query MySql.
Se aggiungiamo che le espressioni regolari in sè sono un poco pesanti da digerire, almeno a prima vista, ne consegue che non è mai stata praticamente affrontata la questione in questo forum.
Cercherò allora di spiegare a grandi linee come si possono utilizzare.

Cominciamo col rinfrescare un pò la memoria, soprattutto a chi non conosce bene l'SQL.
In una interrogazione, per selezionare le righe di una tabella che contengono il valore 'casa' all'interno di un campo è sufficiente questa query:

SELECT campo FROM tabella WHERE campo = 'casa';

ma cosa succede se, in coda a 'casa', aggiungiamo il carattere '%' ?
Innanzitutto dobbiamo sostituire l'operatore di confronto '=' con 'LIKE':

SELECT campo FROM tabella WHERE campo LIKE 'casa%';

ora possiamo recuperare le righe che contengono: 'casa' e 'casalinga'.
Allo stesso modo, inserendo il carattere '%' all'inizio:

SELECT campo FROM tabella WHERE campo LIKE '%casa%';

otterremo oltre a 'casa' e 'casalinga' anche 'accasato'.
Ricordiamoci quindi che '%' consente la sostituzione di un numero indefinito di caratteri.
L'altra wild card disponibile è l'underscore '_' che consente la sostituzione di un singolo carattere:

SELECT campo FROM tabella WHERE campo LIKE 'ca_a';

estrae le righe che contengono 'casa', 'cava' e 'cara'.

Fatta questa premessa, immaginiamo di avere una tabella con un campo che contiene queste righe:

Buon Natale
Buone Feste
Buon Anno
Buona Pasqua

Se io voglio recuperare le righe che contengono la parola 'Buon', come mi comporto?
Non posso usare:

SELECT campo FROM tabella WHERE campo = 'Buon';

perchè confronta la corrispondenza esatta del campo;
non posso nemmeno usare:
SELECT campo FROM tabella WHERE campo LIKE 'Buon%';

in questo caso mi restituirebbe tutte e quattro le righe!
E' quì che entra in gioco la potenza delle estressioni regolari, supportate benissimo da MySql (conformi al modello POSIX).

Proviamo a modificare la nostra query in questo modo:

SELECT campo FROM tabella WHERE campo REGEXP '^Buon$|^Buon[[:space:]]|[[:space:]]Buon[[:space:]]|[[:space:]]Buon$';

e quì qualcuno si spaventerà, sono d'accordo, però è più semplice di quanto sembri a prima vista.
Intanto abbiamo raggiunto il nostro scopo, in quanto siamo riusciti ad estrarre le righe che contengono:

Buon Natale
Buon Anno


Analizziamo ora l'espressione regolare utilizzata:

'^Buon$|^Buon[[:space:]]|[[:space:]]Buon[[:space:]]|[[:space:]]Buon$'

in parole povere possiamo descriverla così, partendo da sinistra verso destra:

^Buon$ cerca la parola 'Buon' che comincia all'inizio del campo (^) e termina alla fine del campo ($)
| oppure
^Buon[[:space:]] cerca la parola 'Buon' che comincia all'inizio del campo (^) ed è seguita da uno spazio ([[:space:]])
| oppure
[[:space:]]Buon[[:space:]] cerca la parola 'Buon' che è preceduta e seguita da uno spazio ([[:space:]])
| oppure
[[:space:]]Buon$ cerca la parola 'Buon' che è preceduta da uno spazio ([[:space:]]) e termina alla fine del campo ($)


Una espressione regolare può usare uno o più dei sequenti caratteri/costrutti:

^
Ricerca la corrispondenza all'inizio della stringa
'Buon' REGEXP '^Bu' -> vero
'Buon' REGEXP '^uo' -> falso

$
Ricerca la corrispondenza alla fine della stringa
'Buon' REGEXP 'on$' -> vero
'Buon' REGEXP 'Buo$' -> falso

.
Ricerca la corrispondenza di un carattere
'Buon' REGEXP 'Buo.' -> vero
'Buon' REGEXP 'Buon.' -> falso

a*
Ricerca la corrispondenza di zero o più caratteri
'Buon' REGEXP 'u*' -> vero
'Buon' REGEXP 'x*' -> vero

a+
Ricerca la corrispondenza di uno o più caratteri
'Buon' REGEXP 'u+' -> vero
'Buon' REGEXP 'x+' -> falso

a?
Ricerca la corrispondenza di zero o un carattere
'Buon' REGEXP 'u?' -> vero
'Buun' REGEXP 'u?' -> falso

Bu|on
Ricerca la corrispondenza di una delle sequenze indicate
'Buon' REGEXP 'Bu|on' -> vero
'BuXn' REGEXP 'Bu|on' -> falso

(uo)
Confronta zero o più istanze della sequenza indicate
'Buon' REGEXP '(uo)' -> vero
'BuXn' REGEXP '(uo)' -> falso

a{0,1}
Una alternativa all'utilizzo di a*, a+, a?:
a{3} confronta una sequenza esatta di 3 caratteri (a)
a{0,} confronta una sequenza di 0 o più caratteri (a)
a{2,3} confronta una sequenza di almeno 2 e fino a 3 caratteri (a)

[a-dX]
[^a-dX]
Confronta i caratteri che sono, oppure non sono se viene utilizzato (^), contenuti nel pattern indicato
'Buon' REGEXP '[a-cX]' -> vero
'BuXn' REGEXP '[d-fX]' -> vero

[:classe_di_caratteri:]
Le classi di caratteri possono essere utilizzate racchiuse tra [[: e :]]
Le classi di caratteri sono:
alnum, alpha, blank, cntrl, digit, graph, lower, print, punct, space, upper, xdigit.
'Buon' REGEXP '[[:alpha:]]+' -> vero
'!. !' REGEXP '[[:alpha:]]+' -> falso


Ulteriori informazioni possono essere recuperate nel manuale MySql alla sezione:
Description of MySql regular expression syntax


Nella speranza di aver fatto cosa gradita, auguro di passare delle Buone e Serene Feste a tutti quanti.

[gm]

Pillola aggiunta al thread in rilievo

[Falcao]

:metallica :metallica

Grandissimo Gianfry!!!

ora voglio fare una domanda quasi inutile:

naturalmente utilizzando le espressioni regolari la ricerca diventa "case sensitive" vero??

[gianfry]

Giusto Falcao, mi scuso per il ritardo della risposta, ma una integrazione a quanto detto sopra è doverosa, e riguarda il confronto tra minuscole e maiuscole:

REGEXP non prevede parametri tipo /i (come le espressioni regolari Perl), nè si differenzia come il PHP che utilizza, ad esempio, le funzioni ereg() ed eregi().

Semplicemente REGEXP avrà sempre un comportamento 'case-insensitive'.

La soluzione, se vogliamo fare un confronto case-sensitive, è quella di utilizzare l'operatore BINARY su uno dei due operandi.

Se, ad esempio, nella mia tabella ho queste righe:

Buona Natale
buon anno
buona Pasqua

con questa query:

SELECT * FROM tabella WHERE campo REGEXP BINARY '^b';

riuscirò ad estrarre le seguenti righe:

buon anno
buona Pasqua

Ciao

[kuarl]

esatto, per aggiungere un paio di palore tecniche in questo modo si fa' un casting forzato del tipo dell'espressione, in questo modo è come se fosse di tipo text o blob nelle quali le ricerce di stringhe sono sempre case-sensitive. Dato quindi che facciamo una ricerca VARCHAR - BLOB il tipo VARCHAR viene convertito forzatamente in BLOB xke BLOB ha priorità più alta di VARCHAR

[bubu sette sette]

Non ho ancora letto ma sono sicuro che sia una grande pillola:metallica



Spero di avere presto due minuti per leggerla

[gianfry]

Originariamente inviato da bubu sette sette
Non ho ancora letto ma sono sicuro che sia una grande pillola:metallica

una 'grande pillola'?
se vuoi possiamo chiamarla supposta... :gren:

[max161]

ad esempio qui è stringa pura:
SELECT campo FROM tabella WHERE campo REGEXP '^Buon$';

ma qui?

SELECT campo FROM tabella WHERE campo REGEXP '^$var$';
i 2 $ non creano conflitto?
devo forse fare così?


SELECT campo FROM tabella WHERE campo REGEXP '^".$var."$';

[xnavigator]

Originariamente inviato da gianfry
Se io voglio recuperare le righe che contengono la parola 'Buon', come mi comporto?
Non posso usare:

SELECT campo FROM tabella WHERE campo = 'Buon';

perchè confronta la corrispondenza esatta del campo;
non posso nemmeno usare:
SELECT campo FROM tabella WHERE campo LIKE 'Buon%';

non è vero... basta fare:
SELECT campo FROM tabella WHERE campo LIKE 'Buon %';

comunque ho un'altra domanda: se da un form mi arriva una variabile cosi fatta "a b" come faccio a prendere i record contenenti "a c b" o "a bchgjwasn b" ?

devo usare explode(" ",$variabileform); per poi organizzarmi la select? nella pillola non ho trovato una soluzione

[fisica]

se io voglio eliminare lo spazio finale da tutti i campi di una colonna che lo contengono come faccio?

ovvero queste espressioni valgono anche con UPDATE? come?