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
|||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
