SQL Stored Procedure LIKE
This article could help you by your problem:
http://sqlperformance.com/2012/07/t-sql-queries/split-strings
In this article Aaron Bertrand is writing about your problem.It's really long and very detailed.
One Way would be this:
CREATE FUNCTION dbo.SplitStrings_XML( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255))RETURNS TABLEWITH SCHEMABINDINGAS RETURN ( SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)') FROM ( SELECT x = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') + '</i>').query('.') ) AS a CROSS APPLY x.nodes('i') AS y(i) );GO
With this function you only call:
WHERE AREA IN (SELECT Item FROM dbo.SplitStrings_XML(@communityDesc, N','))
Hope this could help you.
The simplest way to use this variable is:
SELECT * FROM somethingWHERE ',' + @communityDesc + ',' Like '%,' + AREA + ',%'
this is for tsql, for oracle use ||
to concatenate strings
In
only works with sets of values, not with characters in a string. To answer your question technically, the only way you could do this is to create a set of values representing the three values 'aaa', 'bbb' & 'ccc' and then put those three values into a table (a Temp Table or table variable (in SQL Server), and then perform IN against that set of values (against the table:
declare @Vals table (value varchar(20))insert @vals(Value) Values('aaa')insert @vals(Value) Values('bbb')insert @vals(Value) Values('ccc')select * from SomeOtherTable Where SomeColumn IN (Select value from @vals)
To create the set you would need to create an empty temp table or table variable to hold this set of values, parse the comma delimited string into individual values, and enter those individual values into the temp table or table variable.
although you don't say, if you are using SQL Server, the following is a SQL Server User Defined function (UDF) that will parse a delimited string and return a table with one row for each delimted value:
if you create the UDF, then you would use it as follows:
select * from SomeOtherTable Where SomeColumn IN (Select sVal from dbo.ParseSTring(@communityDesc, ','))
/****** Object: UserDefinedFunction [dbo].[ParseString] Script Date: 4/8/2016 1:53:00 PM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER FUNCTION [dbo].[ParseString] (@S Text, @delim VarChar(5))Returns @tOut Table (ValNum Integer Identity Primary Key, sVal VarChar(8000))AsBegin Declare @dLLen TinyInt -- Length of delimiterDeclare @sWin VarChar(8000)-- Will Contain Window into text stringDeclare @wLen Integer -- Length of WindowDeclare @wLast TinyInt -- Boolean to indicate processing Last WindowDeclare @wPos Integer -- Start Position of Window within Text StringDeclare @sVal VarChar(8000)-- String Data to insert into output TableDeclare @BtchSiz Integer -- Maximum Size of WindowSet @BtchSiz = 7900 -- (Reset to smaller values to test routine)Declare @dPos Integer -- Position within Window of next DelimiterDeclare @Strt Integer -- Start Position of each data value in Window-- ---------------------------------------------------------------- ---------------------------If @delim is Null Set @delim = '|'If DataLength(@S) = 0 Or Substring(@S, 1, @BtchSiz) = @delim Return-- ---------------------------Select @dLLen = Len(@delim), @Strt = 1, @wPos = 1, @sWin = Substring(@S, 1, @BtchSiz)Select @wLen = Len(@sWin), @wLast = Case When Len(@sWin) = @BtchSiz Then 0 Else 1 End, @dPos = CharIndex(@delim, @sWin, @Strt)-- ----------------------------While @Strt <= @wLen Begin If @dPos = 0 Begin -- No More delimiters in window If @wLast = 1 Set @dPos = @wLen + 1 Else Begin Set @wPos = @wPos + @Strt - 1 Set @sWin = Substring(@S, @wPos, @BtchSiz) -- ------------------------------------- Select @wLen = Len(@sWin), @Strt = 1, @wLast = Case When Len(@sWin) = @BtchSiz Then 0 Else 1 End, @dPos = CharIndex(@delim, @sWin, 1) If @dPos = 0 Set @dPos = @wLen + 1 End End -- ------------------------------- Set @sVal = LTrim(Substring(@sWin, @Strt, @dPos - @Strt)) Insert @tOut (sVal) Values (@sVal) -- ------------------------------- -- Move @Strt to char after last delimiter Set @Strt = @dPos + @dLLen Set @dPos = CharIndex(@delim, @sWin, @Strt) EndReturn End