Dove valore nella colonna contenente valori delimitati da virgola

Ciao mi chiedo come scrivere un’istruzione SQL per SQL Server 2008 che seleziona la voce in cui una colonna contiene un valore, ora il valore all’interno della colonna è un elenco delimitato da virgole (di solito – potrebbe esserci solo una voce (e nessuna virgola principale) ), quindi, in Che cosa è Verificare è “questo valore è contenuto da qualche parte all’interno dell’elenco?”, ad esempio:

COLUMN = Cat, Dog, Sparrow, Trout, Cow, Seahorse Does COLUMN contain Cat? YES Does COLUMN contain horse? NO Does COLUMN contain Sheep? NO 

o

 COLUMN = Mouse Does COLUMN contain Hare? NO Does COLUMN contain Mouse? YES 

eccetera

Stavo pensando che potrei usare la parola chiave “IN” in quanto tale

 SELECT id_column FROM table_name WHERE 'Cat' IN COLUMN 

ma questo non funziona in quanto sembra che tu possa usarlo solo per verificare se una colonna contiene una serie di valori delimitati da virgole.

Inoltre non posso usare CONTAINS () O ‘LIKE’ come questo, nell’esempio sopra restituirei valori per ‘horse’ come l’intera stringa contiene horse in ‘Seahorse’, e non riesco a cercare l’ago più una virgola (se Sto cercando ‘cavallo’ la ricerca sarebbe ‘cavallo’, ‘) come se la voce fosse alla fine di una lista? E non posso cercare una virgola più un ago (se sto cercando ‘cavallo’ la ricerca sarebbe ‘, cavallo’) come se la voce fosse la prima della lista? E non posso usare entrambi come se la voce fosse l’unica (singola) entrata?

Grazie per l’aiuto che chiunque può dare.

Saluti.

C’è uno scenario difficile. Se cerco ’40’ nella lista ’17, 34,400,12 ‘, allora troverà “, 40” e restituirà quella voce errata. Questo si prende cura di tutte le soluzioni:

 WHERE (',' + RTRIM(MyColumn) + ',') LIKE '%,' + @search + ',%' 
 WHERE MyColumn LIKE '%,' + @search + ',%' --middle OR MyColumn LIKE @search + ',%' --start OR MyColumn LIKE '%,' + @search --end OR MyColumn = @search --single (good point by Cheran S in comment) 
 SELECT * FROM TABLENAME WHERE FIND_IN_SET(@search, column) 

Se risulta che la tua colonna ha degli spazi tra gli elementi dell’elenco, usa

 SELECT * FROM TABLENAME WHERE FIND_IN_SET(@search, REPLACE(column, ' ', '')) 

http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

 DECLARE @search VARCHAR(10); SET @search = 'Cat'; WITH T(C) AS ( SELECT 'Cat, Dog, Sparrow, Trout, Cow, Seahorse' ) SELECT * FROM T WHERE ', ' + C + ',' LIKE '%, ' + @search + ',%' 

Ciò richiederà ovviamente una scansione completa della tabella per ogni ricerca.

La soluzione migliore in questo caso è normalizzare la tabella in modo che i valori separati da virgole siano in righe diverse (First normal form 1NF) http://en.wikipedia.org/wiki/First_normal_form

Per questo, è ansible implementare una bella funzione di valore di tabella divisa in SQL, utilizzando CLR http://bi-tch.blogspot.com/2007/10/sql-clr-net-function-split.html o utilizzando SQL semplice.

 CREATE FUNCTION dbo.Split ( @RowData nvarchar(2000), @SplitOn nvarchar(5) ) RETURNS @RtnValue table ( Id int identity(1,1), Data nvarchar(100) ) AS BEGIN Declare @Cnt int Set @Cnt = 1 While (Charindex(@SplitOn,@RowData)>0) Begin Insert Into @RtnValue (data) Select Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1))) Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData)) Set @Cnt = @Cnt + 1 End Insert Into @RtnValue (data) Select Data = ltrim(rtrim(@RowData)) Return END 

Quindi è ansible interrogare l’output normalizzato usando cross apply

 select distinct a.id_column from MyTable a cross apply dbo.Split(A.MyCol,',') b where b.Data='Cat' 

Ho trovato questa risposta su un altro forum, funziona perfettamente. Nessun problema con la ricerca di 1 se c’è anche un 10

 WHERE tablename REGEXP "(^|,)@search(,|$)" 

L’ho trovato qui

 select * from YourTable where ','+replace(col, ' ', '')+',' like '%,Cat,%' 

Ho appena saputo di questo quando stavo cercando una soluzione per un problema simile. SQL ha una nuova parola chiave chiamata CONTAINS che puoi usare. Per ulteriori dettagli, vedere http://msdn.microsoft.com/en-us/library/ms187787.aspx

Poiché non si conoscono le voci delimitate da virgole che è ansible trovare, potrebbe essere necessario creare una funzione con le funzioni di SQL Server “charindex” e “sottostringa”. I valori, restituiti dalla funzione, potrebbero essere utilizzati in un’espressione “in”.

La funzione può essere richiamata in modo ricorsivo o è ansible creare un ciclo, cercando le voci fino a quando non sono presenti più voci nella stringa. Ogni chiamata alla funzione utilizza l’indice trovato precedente come punto di partenza della prossima chiamata. La prima chiamata inizia da 0.

 SELECT * FROM TABLE_NAME WHERE ( LOCATE(',DOG,', CONCAT(',',COLUMN,','))>0 OR LOCATE(',CAT,', CONCAT(',',COLUMN,','))>0 ); 

Se conosci gli ID anziché le stringhe, utilizza questo approccio:

 where mylookuptablecolumn IN (myarrayorcommadelimitedarray) 

Assicurati che myarrayorcommadelimitedarray non sia messo tra virgolette.

funziona se vuoi A o B, ma non AND.

Anche se la soluzione complicata @ tbaxter120 consigliata è buona, ma io uso questa funzione e lavoro come un incantesimo, pString è una stringa delimitata e pDelimiter è un carattere delimitatore:

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[DelimitedSplit] --===== Define I/O parameters (@pString NVARCHAR(MAX), @pDelimiter CHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover VARCHAR(8000) WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ), cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT 1 UNION ALL -- does away with 0 base CTE, and the OR condition in one go! SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,tN,1) = @pDelimiter ), cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT s.N1, ---ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000) ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,50000) FROM cteStart s ) --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), Item = SUBSTRING(@pString, l.N1, l.L1) FROM cteLen l ; 

Quindi, ad esempio, puoi chiamarlo dove clausola come di seguito:

 WHERE [fieldname] IN (SELECT LTRIM(RTRIM(Item)) FROM [dbo].[DelimitedSplit]('2,5,11', ',')) 

Spero che questo aiuto.

Dove valore nella colonna contenente valori delimitati da virgole cercare con più virgola delimitata

  declare @d varchar(1000)='-11,-12,10,121' set @d=replace(@d,',',',%'' or '',''+a+'','' like ''%,') print @d declare @d1 varchar(5000)= 'select * from ( select ''1,21,13,12'' as a union select ''11,211,131,121'' union select ''411,211,131,1211'') as t where '',''+a+'','' like ''%,'[email protected]+ ',%''' print @d1 exec (@d1)