in

Corey Roth and Friends Blogs

Group site for developer blogs dealing with (usually) Ionic, .NET, SharePoint, Office 365, Mobile Development, and other Microsoft products, as well as some discussion of general programming related concepts.

Kyle Kelin on .Net

Searching and Comma-Delimited Strings in SQL

I recently needed to write a stored procedure that would take a search string from a web form and search three columns: name, description, and keywords. The keywords column and the input string can both be comma delimited. The key to the sproc is going to be the SQL IN operator. The IN operator can be used in a where clause to filter based on a list like so:

Where

    id IN (1,4,6,10,13)

 

So what I needed was:

 

Where

    id IN (@searchString)

 

 

But you can't do that so I needed to write a split function to split out @searchString. Note: I think there maybe be a split function in SQL Server 2005/2008 but I was using SQL Express.

 

Here is the final result:

ALTER FUNCTION dbo.Split

(

    @List nvarchar(2000),

    @SplitOn nvarchar(5)

)

RETURNS @RtnValue table

(

        

    Id int identity(1,1),

    Value nvarchar(100)

)

AS

BEGIN

 

While (Charindex(@SplitOn,@List)>0)

Begin

 

Insert Into @RtnValue (value)

Select

Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))

 

Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))

End

 

Insert Into @RtnValue (Value)

Select Value = ltrim(rtrim(@List))

 

Return

END

-------------------------------------------------------------------------------------

ALTER PROCEDURE SearchApplications

    (

    @searchWord varchar(100)

 

    )

AS

SELECT ID, [Name], Description FROM Applications

 

WHERE

[Keywords] IN (Select Value from dbo.Split(@searchWord,',')) or

[Name] IN (Select Value from dbo.Split(@searchWord,',')) or

[Name] = @searchWord or

[Description] IN (Select Value from dbo.Split(@searchWord,',')) or

[Description] = @searchWord

 

Comments

 

bryankia said:

Kyle

That is a slick answer to a problem I have run into a few times.

Thanks,

Bryan

August 29, 2008 9:11 AM

Leave a Comment

(required)
(optional)
(required)
Add

About KyleKelin

Kyle Kelin has been implementing software in the Microsoft space for the past 6 years mainly as a consultant. His interests are SharePoint, .NET, JQuery, and Silverlight.
2019.
Powered by Community Server (Non-Commercial Edition), by Telligent Systems