È ansible interrogare una colonna separata da virgole per un valore specifico?

Ho (e non possiedo, quindi non posso cambiare) una tabella con un layout simile a questo.

ID | CATEGORIES --------------- 1 | c1 2 | c2,c3 3 | c3,c2 4 | c3 5 | c4,c8,c5,c100 

Devo restituire le righe che contengono un ID di categoria specifico. Iniziando scrivendo le query con le istruzioni LIKE, perché i valori possono essere ovunque nella stringa

SELECT id FROM table WHERE categories LIKE '%c2%'; Restituirebbe le righe 2 e 3

SELECT id FROM table WHERE categories LIKE '%c3%' and categories LIKE '%c2%'; Vorrei di nuovo ottenere le righe 2 e 3, ma non la riga 4

SELECT id FROM table WHERE categories LIKE '%c3%' or categories LIKE '%c2%'; Vorrei di nuovo ottenere le righe 2, 3 e 4

Non mi piacciono tutte le dichiarazioni LIKE . Ho trovato FIND_IN_SET() nella documentazione di Oracle ma non sembra funzionare in 10g. Ottengo il seguente errore:

 ORA-00904: "FIND_IN_SET": invalid identifier 00904. 00000 - "%s: invalid identifier" 

quando si esegue questa query: SELECT id FROM table WHERE FIND_IN_SET('c2', categories); (esempio dai documenti) o questa query: SELECT id FROM table WHERE FIND_IN_SET('c2', categories) 0; (esempio da Google)

Mi aspetto che restituisca le righe 2 e 3.

C’è un modo migliore per scrivere queste query invece di utilizzare una tonnellata di istruzioni LIKE ?

Puoi, usando LIKE. Non vuoi corrispondere per i valori parziali, quindi dovrai includere le virgole nella tua ricerca. Ciò significa anche che dovrai fornire una virgola aggiuntiva per cercare valori all’inizio o alla fine del testo:

 select * from YourTable where ',' || CommaSeparatedValueColumn || ',' LIKE '%,SearchValue,%' 

Ma questa query sarà lenta, così come tutte le query che utilizzano LIKE, specialmente con un carattere jolly principale.

E c’è sempre un rischio. Se ci sono spazi attorno ai valori, o i valori possono contenere delle virgole stesse nel qual caso sono circondati da virgolette (come nei file CSV), questa query non funzionerà e dovrai aggiungere ancora più logica, rallentando la query ancora di più.

Una soluzione migliore sarebbe aggiungere una tabella figlio per queste categorie. O meglio, anche una tabella separata per le categorie e una tabella che li collega a YourTable.

È ansible scrivere una funzione di tabella PIPELINED che restituisce una tabella di 1 colonna. Ogni riga è un valore della stringa separata da virgola. Usa qualcosa di simile per far pop una stringa dalla lista e inserirla come una riga nella tabella:

 PIPE ROW(ltrim(rtrim(substr(l_list, 1, l_idx - 1),' '),' ')); 

Uso:

 SELECT * FROM MyTable WHERE 'c2' IN TABLE(Util_Pkg.split_string(categories)); 

Vedi di più qui: documenti Oracle

Sì e no…

“Sì”:

Normalizza i dati (fortemente consigliato) – cioè dividi la colonna delle categorie in modo che tu abbia ciascuna categoria in un separato … quindi puoi semplicemente interrogarlo in un normale modello …

“No”:
Finché manterrai questa “pseudo-struttura” ci saranno diversi problemi (prestazioni e altro) e dovrai fare qualcosa di simile a:

 SELECT * FROM MyTable WHERE categories LIKE 'c2,%' OR categories = 'c2' OR categories LIKE '%,c2,%' OR categories LIKE '%,c2' 

SE è assolutamente necessario definire una funzione che si chiami FIND_IN_SET come la seguente:

 CREATE OR REPLACE Function FIND_IN_SET ( vSET IN varchar2, vToFind IN VARCHAR2 ) RETURN number IS rRESULT number; BEGIN rRESULT := -1; SELECT COUNT(*) INTO rRESULT FROM DUAL WHERE vSET LIKE ( vToFine || ',%' ) OR vSET = vToFind OR vSET LIKE ('%,' || vToFind || ',%') OR vSET LIKE ('%,' || vToFind); RETURN rRESULT; END; 

Puoi quindi usare quella funzione come:

 SELECT * FROM MyTable WHERE FIND_IN_SET (categories, 'c2' ) > 0; 

Per motivi di futuri utenti, non dimenticare il modo di espressione regolare:

 with tbl as ( select 1 ID, 'c1' CATEGORIES from dual union select 2 ID, 'c2,c3' CATEGORIES from dual union select 3 ID, 'c3,c2' CATEGORIES from dual union select 4 ID, 'c3' CATEGORIES from dual union select 5 ID, 'c4,c8,c5,c100' CATEGORIES from dual ) select * from tbl where regexp_like(CATEGORIES, '(^|\W)c3(\W|$)'); ID CATEGORIES ---------- ------------- 2 c2,c3 3 c3,c2 4 c3 

Questo corrisponde a un limite di parole, quindi anche se la virgola fosse seguita da uno spazio, funzionerebbe ancora. Se si desidera essere più rigorosi e corrispondere solo dove una virgola separa i valori, sostituire “\ W” con una virgola. Ad ogni modo, leggi l’espressione regolare come: abbina un gruppo dell’inizio della linea o del limite di una parola, seguito dal valore di ricerca objective, seguito da un gruppo di un limite di parole o la fine della linea.

Finché l’elenco delimitato da virgole è pari o inferiore a 512 caratteri, in questa istanza è anche ansible utilizzare un’espressione regolare (le funzioni di espressione regolare di Oracle, ad esempio REGEXP_LIKE() , sono limitate a 512 caratteri):

 SELECT id, categories FROM mytable WHERE REGEXP_LIKE('c2', '^(' || REPLACE(categories, ',', '|') || ')$', 'i'); 

In quanto sopra, sostituisco le virgole con l’operatore di alternanza delle espressioni regolari | . Se il tuo elenco di valori delimitati è già | -delimitato, tanto meglio.