SQL Stored Procedure LIKE SQL Stored Procedure LIKE sql sql

SQL Stored Procedure LIKE

This article could help you by your problem:


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