This is a simple question and I can't seem to think of a solution.
I have this defined in my stored procedure:
@communityDesc varchar(255) = NULL
@communityDesc is "aaa,bbb,ccc"
and in my actual query I am trying to use IN
WHERE AREA IN (@communityDesc)
but this will not work because my commas are inside the string instead of like this "aaa", "bbb", "ccc"
So my question is, is there anything I can do to @communityDesc so it will work with my IN statement, like reformat the string?
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 TABLE
WITH SCHEMABINDING
AS
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 something
WHERE ',' + @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 ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ParseString] (@S Text, @delim VarChar(5))
Returns @tOut Table
(ValNum Integer Identity Primary Key,
sVal VarChar(8000))
As
Begin
Declare @dLLen TinyInt -- Length of delimiter
Declare @sWin VarChar(8000)-- Will Contain Window into text string
Declare @wLen Integer -- Length of Window
Declare @wLast TinyInt -- Boolean to indicate processing Last Window
Declare @wPos Integer -- Start Position of Window within Text String
Declare @sVal VarChar(8000)-- String Data to insert into output Table
Declare @BtchSiz Integer -- Maximum Size of Window
Set @BtchSiz = 7900 -- (Reset to smaller values to test routine)
Declare @dPos Integer -- Position within Window of next Delimiter
Declare @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)
End
Return
End
You can do this by splitting your string using a split function provided here. The function returns a table having a single column which holds your tokens (i.e. 'aaa', 'bbb' ...).
Your query should look like this:
-- get the splits
SELECT Name INTO #someTemp
FROM dbo.splitstring(@communityDesc)
-- get data where area in within description
SELECT 1
FROM yourTable T
WHERE EXISTS (SELECT 1 FROM #someTemp tmp WHERE T.Area = tmp.Name)
A different approach is to use CHARINDEX(). However, using a function in a WHERE clause will slow down the performance.
WHERE CHARINDEX(','+area+',',','+@CommunityDec+',')> 0
If you area field is always 3 letters, you can simplify this.
WHERE CHARINDEX(area,@CommunityDec)> 0
This is a quick solution, but also a stop gap. A better solution is to change the string lookup approach to build a table with one row per search criteria and using a JOIN or sub query.
at first you most create a function to split string some thing like this code
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
then you can use this function is your query like this
WHERE AREA IN (dbo.splitstring(@communityDesc))
You can simply split this csv using XML and use this to filter in your query. No need to use a User defined function or @Table_Valiable or #Temp_Table here.
DECLARE @xml as xml,@communityDesc varchar(255) = 'aaa,bbb,ccc'
SET @xml = cast(('<X>'+replace(@communityDesc,',' ,'</X><X>')+'</X>') as xml)
SELECT * FROM TABLE1
WHERE AREA IN (
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)
)
If you required this split values in further process, then you can Insert this to a #table_Variable or #Temp_Table and use them.
