Keyword Search Help

[blaw1010]

I currently am looking for a solution to solve a 'keyword search' problem. I am using the 'like' functionality to retrieve the values that match the keyword that is entered. However, how can I make the search a multiple keyword search using semicolons to seperate the words?? For example: The user should be able to enter 'admin; manager; director;'.

Any input would be greatly appreciated. Thanks

Here is my stored procedure:


create procedure procSearch

@TrainingCertification varchar(50) = null

as
begin

select *
from TrainingCertifications
where TrainingCertificationsType LIKE '%' + @TrainingCertification + '%'

end;

[shreyaas]

I am a newbie..but what I guess is:
try to do this in 2 procedures instead of one. The inner procedure should find out all key words by slicing the userinput with the delimiter like ';' and the outer procedure then takes each of this slice to do the search..

[Meme]

you need to use code to break up what they enter into the textbox into different variables, which you then use either - use individually in one repeatedl stored procedure. Or, use in a single stored procedure with many parameters.

e.g. code something a bit like ...

string mySearchTerm = txbSearch.Text;
string myParameter = mySearchTerm.Substring(mySearchTerm.IndexOf(";"), mySearchTerm.IndexOf(" "));

then call your stored procedure.

Hoep that helps.

[Richard S]

you need to use code to break up what they enter into the textbox into different variables, which you then use either - use individually in one repeatedl stored procedure. Or, use in a single stored procedure with many parameters.

e.g. code something a bit like ...

string mySearchTerm = txbSearch.Text;
string myParameter = mySearchTerm.Substring(mySearchTerm.IndexOf(";"), mySearchTerm.IndexOf(" "));

then call your stored procedure.

Hoep that helps.

altough it aint my thread, id like to thank you alot for this! helped me out alot! ty man

[sayedul]

Hi,

You can make a sql function with a parameter string to return a table with all the semicolon separate words as in different records. Use the function in the stored procedure. Use cursor to get different words from the table returned by the function within the stored procedure. Use the select statement for different words in cursor loop to fill a temporary table. Finally return the temporary table records. This should serve your requirement. You can ask me for the detailed script if you still cannot prepare the script.

Thanks.

Sayedul

I currently am looking for a solution to solve a 'keyword search' problem. I am using the 'like' functionality to retrieve the values that match the keyword that is entered. However, how can I make the search a multiple keyword search using semicolons to seperate the words?? For example: The user should be able to enter 'admin; manager; director;'.

Any input would be greatly appreciated. Thanks

Here is my stored procedure:


create procedure procSearch

@TrainingCertification varchar(50) = null

as
begin

select *
from TrainingCertifications
where TrainingCertificationsType LIKE '%' + @TrainingCertification + '%'

end;

[iburyak]

If you are interested in database solution try this:

1. create function on database side.

[PHP]CREATE FUNCTION Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int, Items nvarchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @ID int

SELECT @INDEX = 1, @ID = 1
WHILE @INDEX !=0


BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
SELECT @ID = @ID + 1
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
[/PHP]

2. Create procedure.

[PHP]create procedure procSearch

@TrainingCertification varchar(50) = null

as
begin

select *
from TrainingCertifications
join Split(@TrainingCertification, ';') on charindex(items, LTRIM(RTRIM(TrainingCertificationsType))) > 0

end[/PHP]


I can't test this code but I tested on my database with my tables and it worked.

Good Luck.

[sayedul]

Hi,

The detail of the DB solution is greate especially the select statement written in the stored procedure.

Thanks.

If you are interested in database solution try this:

1. create function on database side.

[PHP]CREATE FUNCTION Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (ID int, Items nvarchar(4000))
AS

BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
DECLARE @ID int

SELECT @INDEX = 1, @ID = 1
WHILE @INDEX !=0


BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(ID, Items) VALUES(@ID, @SLICE)
SELECT @ID = @ID + 1
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END
[/PHP]

2. Create procedure.

[PHP]create procedure procSearch

@TrainingCertification varchar(50) = null

as
begin

select *
from TrainingCertifications
join Split(@TrainingCertification, ';') on charindex(items, LTRIM(RTRIM(TrainingCertificationsType))) > 0

end[/PHP]


I can't test this code but I tested on my database with my tables and it worked.

Good Luck.

[karthickbabu]

I am a newbie..but what I guess is:
try to do this in 2 procedures instead of one. The inner procedure should find out all key words by slicing the userinput with the delimiter like ';' and the outer procedure then takes each of this slice to do the search..

Hi

Create FULL TEXT CATALOG in Sql Server. Search how to create Catalog in online or Sql Server Books online help. Its very easy to find keyword search help. Its ack like small search engine. It creating character based index in DataBase. When you type keyword and search it will shows your required search

way to create full text catalog:
1. Expand a server group, and then expand a server.
2. Expand Databases, and then right-click the database where you want the full- text catalog.
3. Click New, and then click New Full-Text Catalog.
4. Complete the New Full-Text Catalog dialog box.

You must install your Sql Server in Custom Installation. Then only we create Full Text Catalog. We create Full Text Catalog using Sql queries also. search in sql server book online file (chm file). We create schedule for catalog. Its automatically populated while your database update or set the time interval when ever we want the catalog can be populated.

This is the simple way to create keyword search help

[sayedul]

I currently am looking for a solution to solve a 'keyword search' problem. I am using the 'like' functionality to retrieve the values that match the keyword that is entered. However, how can I make the search a multiple keyword search using semicolons to seperate the words?? For example: The user should be able to enter 'admin; manager; director;'.

Any input would be greatly appreciated. Thanks

Here is my stored procedure:


create procedure procSearch

@TrainingCertification varchar(50) = null

as
begin

select *
from TrainingCertifications
where TrainingCertificationsType LIKE '%' + @TrainingCertification + '%'

end;

Hi,

I have written another very simple solution of the problem. The solution is tested. The procedure is as follows:

create procedure procSearch
@TrainingCertification varchar(50) = null
as
begin
select *
from TrainingCertifications
where (';' + replace(@TrainingCertification, '; ', ';') + ';') 
like ('%;' + TrainingCertificationsType + ';%')
end
 

- Sayedul Haque