Le funzioni ELT(), FIELD() e COALESCE()

Gli argomenti di questa pagina

  • La funzione ELT()
  • Esempio teorico dell'uso della funzione ELT()
  • Esempio pratico dell'uso della funzione ELT()
  • La funzione FIELD()
  • Esempio teorico per la funzione FIELD()
  • Esempio pratico dell'uso della funzione FIELD()
  • La funzione COALESCE()
  • Esempio teorico per la funzione COALESCE()
  • Esempio pratico dell'uso della funzione COALESCE()

La funzione ELT()

La funzione ELT() restituisce l'n-esimo elemento della lista di stringhe: str1 se N = 1, str2 se N = 2, e così via. Restituisce null se N è minore di 1 o maggiore del numero di argomenti.
ELT() è il complemento di FIELD().

La sintassi della funzione ELT()

ELT(index, string1, string2, string3,…)

index è un numero intero
string1, string2, ecc è una lista di stringhe separate da una virgola

Esempio teorico dell'uso della funzione ELT()

In questi primi esempi, senza alcuna pretesa ed apparentemente inutili, possiamo possiamo arrivare ad alcune riflessioni: con le prime due Query vengono estratti rispettivamente il quarto ed il secondo elemento della lista, mentre la terza e quarta Query restituiscono 'NULL' perchè gli indici non trovano alcun elemento. Queste Query potrebbero rispondere a questo quesito:

Estrarre e mostrare dalla lista gli elementi indicati dagli indici

esempio 1
1
2
3
4
5
6
7
 		<?php
$Query = "SELECT ELT(4,'Paperino','Pippo','Paperone','Pinco pallino') AS Find_1, 
                ELT(2,'Paperino','Pippo','Paperone','Pinco pallino') AS Find_2, 
                ELT(0,'Paperino','Pippo','Paperone','Pinco pallino') AS Find_3, 
                ELT(7,'Paperino','Pippo','Paperone','Pinco pallino') AS Find_4
";
?>
1 record letti
Find_1 Find_2 Find_3 Find_4
Pinco pallino Pippo NULL NULL

Esempio pratico dell'uso della funzione ELT()

Mostrare la data di nascita dei contatti coi nomi della settimana e/o i nomi dei mesi in italiano

Questo che segue è, invece un esempio pratico da utilizzare in una tabella concreta. Iniziamo col vedere come il MYSQL, con le sue funzioni DAYNAME() e MONTHNAME(), ci restituisce i nomi dei giorni della settimana o i nomi dei mesi. Come si vede dall'esempio qui sotto i nomi vengono restituiti in inglese. Se vogliamo ottenere i nomi dei mesi o i nomi delle settimane in italiano è comodo affidarci a questa funzione.

Nomi dei giorni della settimana
1
2
3
4
5
6
 		<?php
$Query = "SELECT DAYNAME(CURRENT_TIMESTAMP()) AS test1, 
        DAYNAME('2014-04-28 23:28:09') AS test2, 
        DAYNAME(NOW()) AS test3 
"; 
?>
1 record letti
test1 test2 test3
Monday Monday Monday
Nomi dei mesi
1
2
3
4
5
6
7
 		<?php
$Query = "SELECT MONTHNAME(CURRENT_TIMESTAMP()) AS test1, 
        MONTHNAME('2014-02-28 23:28:09') AS test2, 
        MONTHNAME('2014-03-28 23:28:09') AS test3, 
        MONTHNAME(NOW()) AS test4 
"; 
?>
1 record letti
test1 test2 test3 test4
July February March July

Per raggiungere il nostro scopo possiamo usare la funzione ELT() indicando nella lista i nomi desiderati e per dargli una index valida usiamo le funzioni DAYOFWEEK() per i numeri dei giorni della settimana e MONTH() per i numeri dei mesi. In questo modo le due funzioni offrono l'index che serve per scegliere un nome indicato nella lista.

esempio per i nomi dei giorni
1
2
3
4
5
6
7
 		<?php
//    data_nascita    sql_agenda
$Query = "SELECT DATE_FORMAT( data_nascita, '%d-%m-%Y' ) AS data_nascita,
ELT(DAYOFWEEK(data_nascita),'dom','lun','mar','mer','gio','ven','sab') AS Giorno
FROM sql_agenda 
";
?>
103 record trovati
12 record visualizzati
data_nascita Giorno
22-01-2004 gio
22-11-2007 gio
20-08-2004 ven
21-05-2009 gio
12-01-1997 dom
02-07-1987 gio
12-08-2009 mer
14-11-1987 sab
26-07-1987 dom
25-01-1987 dom
26-06-1988 dom
19-07-1987 dom
... ...

La funzione FIELD()

La funzione FIELD() restituisce l'indice (posizione) di str nella lista str1, str2, str3, .... Ma restituisce 0 se str non viene trovato.

La sintassi della funzione FIELD():

FIELD(search string, string1, string2, string3…..)

search string: la stringa da cercare nell'elenco delle stringhe
string1, .......... elenco delle stringhe

Esempio teorico per la funzione FIELD()

Se tutti gli argomenti della lista sono stringhe, tutti gli argomenti sono confrontati come stringhe, se sono numeri sono confrontati come numeri. La funzione non trova una sottostringa, ma la stringa intera.
In caso di fallimento la funzione FIELD() restituisce '0' (zero).
La funzione FIELD() è il complementare di ELT ().

esempio 1
1
2
3
4
5
6
7
8
9
10
 		<?php
$Query = "SELECT 
    FIELD('paperino', 'Paperino','Pippo','Paperone','Pinco pallino') AS trovato1, 
    FIELD('Paperone', 'Paperino','Pippo','Paperone','Pinco pallino') AS trovato2, 
    FIELD('Pinco', 'Paperino','Pippo','Paperone','Pinco pallino') AS trovato3, 
    FIELD('Cesare', 'Paperino','Pippo','Paperone','Pinco pallino') AS trovato4, 
    FIELD(NULL, 5, NULL, 2, 3, 9) AS Scelta1, 
    FIELD(12, NULL, 12, 53, 29) AS Scelta2
";
?>
1 record letti
trovato1 trovato2 trovato3 trovato4 Scelta1 Scelta2
1 3 0 0 0 2

Esempio pratico dell'uso della funzione FIELD()

Vediamo di risolvere un problema un po' insolito.

Mostra l'elenco dei miei contatti, ma metti in cima alla lista i nomi indicati, se esistgenti

In questo caso usiamo la funzione FIELD() nella clausola ORDER BY, usando il nome del campo per dargli il nome del contatto come search string ed alcuni nomi come stringhe da usare nella lista.
In questo modo i valori indicati vengono posti all'inizio o alla fine del resultset, dipendente dal DESC, a prescindere dall'ordine che i nomi avrebbero nel restante elenco ordinato o non ordinato.
In questo esempio ho usato due volte la funzione FIELD(): una per estrapolare i nomi, l'altra per estrapolare un cognome.
Il terzo elemento usato nella clausola ORDER BY è opzionale e permette di effettuare l'ordinamento anche sui restanti nomi.

1
2
3
4
5
6
7
 		<?php
$Query = "SELECT id, cognome, nome
FROM sql_agenda
ORDER BY FIELD( nome, 'Vincenzo', 'Francesco', 'Emilio' ) DESC , 
FIELD( cognome, 'Limone' ) DESC, nome
";
?>
103 record trovati
12 record visualizzati
id cognome nome
37 Bianchi Emilio
59 Valsaca Emilio
60 Zerlenga Francesco
82 Capeci Francesco
62 Forlini Francesco
36 Bialetti Vincenzo
81 Vitulano Vincenzo
31 Limone Maria
42 Zero Agnese
44 Stipa Agnese
75 Deiana Alberino
38 Zeno Alberto
... ... ...

In questo secondo esempio usiamo ancora la funzione FIELD() nella clausola WHERE per rispondere a questa Query:

Estrai dalla tabella dei contatti i nomi indicati nella lista

L'indicazione '> 0' che diamo nella WHERE serve perchè, come abbiamo detto più sopra, la funzione FIELD() restituisce l'indice (posizione) di str nella lista str1, str2, str3, .... Ma restituisce 0 se str non viene trovato.

1
2
3
4
5
6
7
8
9
10
11
 		<?php
$Query = "SELECT cognome, nome
FROM sql_agenda
WHERE FIELD( nome, 'Vincenzo', 'Francesco', 'Emilio' ) > 0
";

$Query1 = "SELECT cognome, nome
FROM sql_agenda
WHERE nome IN ('Vincenzo', 'Francesco', 'Emilio')
";
?>
7 record trovati
7 record visualizzati
cognome nome
Bialetti Vincenzo
Bianchi Emilio
Valsaca Emilio
Zerlenga Francesco
Forlini Francesco
Vitulano Vincenzo
Capeci Francesco

La funzione COALESCE()

La funzione COALESCE() ritorna il primo valore non NULL da una lista, oppure NULL se tutti i falori sono nulli.

La sintassi della funzione COALESCE()

COALESCE(value1,value2,value3,...)

Esempio teorico per la funzione COALESCE()

Nelle righe della Query qui sotto alcuni possibili risultati ottenuti con diverse liste usate con la funzione COALESCE(). Come si noterà, tra le diverse liste date come argomento, ci sono degli elementi vuoti, quindi non NULL. Ebbene, l'elemento vuoto viene regolarmente intercettato.

Mostra il primo valore non NULL dalle seguenti liste

esempio 1
1
2
3
4
5
6
7
8
 		<?php
$Query = "SELECT COALESCE(NULL, 2, 3) AS Find1, 
        COALESCE(NULL, NULL, NULL) AS Find2, 
        COALESCE( NULL, 'Vincenzo', 'Francesco', 'Emilio' ) AS Find3, 
        COALESCE( NULL, '', 'Francesco', 'Emilio' ) AS Find4, 
        COALESCE('Mapoleone', 'Garibaldi', 'Archimede') AS Find5 
";
?>
1 record letti
Find1 Find2 Find3 Find4 Find5
2 NULL Vincenzo Mapoleone

Esempio pratico dell'uso della funzione COALESCE()

Per questa serie di esempi useremo questa tabella. Come si nota dalle poche righe presentate, non tutti i contatti hanno tutti i telefoni e l'indirizzo E-Mail, ma in tutti i record, anche se qui non sono evidenziati per non creare confusione, i campi che qui si vedono vuoti, sono NULL.

105 record trovati
12 record visualizzati
cognome nome tel_casa tel_uff cellulare mail
Migliavacca Luigi NULL 084 41655 NULL NULL
Rizzi Carlo NULL 07 9940508 NULL NULL
Liberali Franca NULL 036 348719 NULL NULL
Di Maggio Mario 012 45 58 61 051 18752 NULL NULL
Scicchitano Andrea NULL 07 74891 NULL NULL
Edelvisi Maurizio NULL NULL NULL [email protected]
Gerardini Silvano NULL 012 164848 NULL NULL
Pasotti Alessio NULL 05 9552713 NULL NULL
Calvi Giovanni NULL 044 5602773 NULL NULL
Poggi Giuseppe NULL 08 0640120 NULL NULL
Curti Oreste NULL 027 42781 NULL NULL
Borsotti Mirella NULL 09 379786 NULL NULL
... ... ... ... ... ...

Vedremo ora una sequenza di esempi sempre più evoluti. In questo primo esempio:

Dei miei contatti mostra il nominativo ed il primo telefono o E-Mail che hanno per essere contattati

In questo caso il telefono trovato o l'indirizzo E-Mail vengono mostrati in una colonna generica qui denominata 'contatto'. Leggendo il resultset ottenuto non è dato sapere che tipo di dato è rappresentato nella colonna 'contatgto'.

esempio 1
1
2
3
4
5
6
 		<?php
$Query = "SELECT cognome, nome , 
COALESCE(tel_casa, tel_uff, cellulare, mail) as contatto 
FROM sql_rubrica_nuova
";
?>
105 record trovati
12 record visualizzati
cognome nome contatto
Migliavacca Luigi 084 41655
Rizzi Carlo 07 9940508
Liberali Franca 036 348719
Di Maggio Mario 012 45 58 61
Scicchitano Andrea 07 74891
Edelvisi Maurizio [email protected]
Gerardini Silvano 012 164848
Pasotti Alessio 05 9552713
Calvi Giovanni 044 5602773
Poggi Giuseppe 08 0640120
Curti Oreste 027 42781
Borsotti Mirella 09 379786
... ... ...

Ora vediamo come aggiustare la Query facendole mostrare anche il tipo di dato estratto.

Dei miei contatti mostra il primo telefono o E-Mail che hanno per essere contattati, ma mostra anche il tipo di dato mostrato

Alla Query vista più sopra aggiungo anche la funzione FIELD() che come primo argomento prende il risultato della funzione COALESCE().
Questa manovra fa sì che la Query restituisca anche un numero che indica quale elemento è stato prelevato. Ma ancora non basta. Nemmeno questo numero è molto eloquente.

esempio 1
1
2
3
4
5
6
7
8
9
 		<?php
$Query = "SELECT cognome, nome , 
COALESCE(tel_casa, tel_uff, cellulare, mail) AS contatto,
FIELD(
    COALESCE(tel_casa, tel_uff, cellulare, mail)
, tel_casa, tel_uff, cellulare, mail) AS tipo_contatto
FROM sql_rubrica_nuova 
";
?>
105 record trovati
12 record visualizzati
cognome nome contatto tipo_contatto
Migliavacca Luigi 084 41655 2
Rizzi Carlo 07 9940508 2
Liberali Franca 036 348719 2
Di Maggio Mario 012 45 58 61 1
Scicchitano Andrea 07 74891 2
Edelvisi Maurizio [email protected] 4
Gerardini Silvano 012 164848 2
Pasotti Alessio 05 9552713 2
Calvi Giovanni 044 5602773 2
Poggi Giuseppe 08 0640120 2
Curti Oreste 027 42781 2
Borsotti Mirella 09 379786 2
... ... ... ...

Per raggiungere il nostro scopo occorre modificare ancora la Query appena vista.
Aggiungiamo la funzione ELT() che prenderà come primo argomento il risultato numerico restituito dalla funzione FIELD() che a sua volta prende come primo argomento il risultato restituito dalla funzione COALESCE().
E' così che nell'ultima colonna il tipo dato che abbiamo prelevato.

esempio 1
1
2
3
4
5
6
7
8
9
10
11
 		<?php
$Query = "SELECT cognome, nome , 
COALESCE(tel_casa, tel_uff, cellulare, mail) AS contatto,
ELT(
    FIELD(
        COALESCE(tel_casa, tel_uff, cellulare, mail)
    , tel_casa, tel_uff, cellulare, mail)
,'tel_casa', 'tel_uff', 'cellulare', 'mail') AS tipo_contatto
FROM sql_rubrica_nuova
";
?>
105 record trovati
12 record visualizzati
cognome nome contatto tipo_contatto
Migliavacca Luigi 084 41655 tel_uff
Rizzi Carlo 07 9940508 tel_uff
Liberali Franca 036 348719 tel_uff
Di Maggio Mario 012 45 58 61 tel_casa
Scicchitano Andrea 07 74891 tel_uff
Edelvisi Maurizio [email protected] mail
Gerardini Silvano 012 164848 tel_uff
Pasotti Alessio 05 9552713 tel_uff
Calvi Giovanni 044 5602773 tel_uff
Poggi Giuseppe 08 0640120 tel_uff
Curti Oreste 027 42781 tel_uff
Borsotti Mirella 09 379786 tel_uff
... ... ... ...

 

 



settore tecnico il sito di lorettabweb il Forum di sostegno
il forum il forum il forum