Problem with Array of strings in SQL...

I am using the code given below which works fine for array of integers, can anyone help me to convert this code to use it for array of strings
basically i want to store the search keywords in the array of strings and use them in the stored procedure.
CREATE Function fnSplitter (@.IDs Varchar(100) )
Returns @.Tbl_IDs Table (ID Int) As

Begin
-- Append comma
Set @.IDs = @.IDs + ','
-- Indexes to keep the position of searching
Declare @.Pos1 Int
Declare @.pos2 Int

-- Start from first character
Set @.Pos1=1
Set @.Pos2=1

While @.Pos1<Len(@.IDs)
Begin
Set @.Pos1 = CharIndex(',',@.IDs,@.Pos1)
Insert @.Tbl_IDs Select Cast(Substring(@.IDs,@.Pos2,@.Pos1-@.Pos2) As Int)
-- Go to next non comma character
Set @.Pos2=@.Pos1+1
-- Search from the next charcater
Set @.Pos1 = @.Pos1+1
End
Return
End
CREATE PROCEDURE spSelectEmployees(@.IDs Varchar(100)) AS
Select * From employees Where employeeid In (Select ID From fnSplitter(@.IDs))

Exec spSelectEmployees '1,4,5,7,9'

I want replace the above Exec statement withExec spSelectEmployees 'sap, abap, hr, ... ' in the JobPosition Column of Employees if possible, i've been trying to change it but its not working out
Thanks in Advance

Check out the split function Bilal posted in this thread:http://forums.asp.net/997824/ShowPost.aspx
|||I checked Haidar Bilals code but it returns only a specific item in the given string based on the position specified.
But in my case i got only one string which goes like this 'sap, abap, consultant,.. , ...' which i need to use them in the stored procedure .
I am actuallycreating a search page for jobs and when a person enters different keywords in the textbox i'm storing them in the array.
Here is the previous part of my code
string strsearch = txtsearch.Text;
string []items = strsearch.Split();
Session["items"]= items;
and on the other page i'm using the session items
string []items = (string[])Session["Items"];


|||

savvy wrote:

I checked Haidar Bilals code but it returns only aspecific item in the given string based on the position specified.

Ack, sorry, I picked the wrong post. There are many posts where asplit function has been posted. There's one that Dinakar postedin this thread:http://forums.asp.net/989365/ShowPost.aspx
|||Thanks for ur fast reply
i used this code
CREATE FUNCTION dbo.Split1
(
@.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
Declare @.list varchar(200)
set @.list = 'SAP MM Support Consultant, Managing Consultant SAP FI/C '
Select * from JobDetails where JobPosition in (Select Data from dbo.Split1(@.list, ','))
I am getting the results but the problem is i have to give the exact word or phrase inorder to get the results, actually i want to enter just 'sap, consultant' and get the same result .. is it possible?
Thanx in Advance
|||You could try your query like this:

SELECT @.list = 'SAP, Consultant'
SELECT
*
FROM
JobDetails AS J
INNER JOIN
dbo.Split1(@.list, ',') AS S ON J.JobPosition LIKE '%' + S.Data + '%'


|||Declare @.list varchar(200)
set @.list = 'abap,hr,sap'
SELECT Distinct J.* FROM JobDetails AS J inner join dbo.Split1(@.list, ',') AS S ON J.JobPosition LIKE '%' + S.Data + '%'
I used this code finally it works fine. Thank u very much Terri Morton for all your help and interestsql