Keyword seach (Not full Phrase) parameterise sql statement

hi,

i want to do search by keywords for e.g "John Smith". should search for "John" and "Smith"

it is easy to do it using dynamic sql statement.

but i am using parameters sql.

this is my sql

"select * from emp_tbl where fname like '%' + @.keyw + '%' or lname like '%' + @.keyw + '%' "

the above sql will search by full phrase

how can i make it search each word in the phrase.

aslo, i am searching for 70-551 exam. to upgrade my mcad to mcts.

can anybody help.

Hi,

The solution is not that difficult. Just do one thing before sending the parameter to the stored procedure. Just replace the white spaces with %, so your query will become something like this

"select * from emp_tbl where fname like '%' + John%Smith + '%' or lname like '%' + John%Smith + '%' "

I am sure it will work for you.

Thanks and best regards,

|||

Hi,

it is not working!!!!.

can anybody advice how to do.

"Dynamic SQL IS EASY. BECUASE I CAN JUST SPLIT THE STRING AND BUILD MY SQL ACCORDINGLY"

|||

Hi,

Also check in Sql Profiler if the values received to the sp are correct as it always works perfectly fine with me. Make sure that the values received by the sp does not contain whitespaces or any non required character.

Thanks and best regards,

|||

hi,

i think you have a records like that

1. John Smith

2. John William Smith

so, in that case it will work perfect. because the records has both the words "John" and "Smith" so the '%' in between will ignore the word "William". this is how it works.

but if you have a records like that

1. John Smith

2. John William

3. Smith Graham.

in this case it will not work.

i need to bring all the records that has the words "John" or "Smith" with a single keyword string "John Smith"

|||

Hi,

You were right, actually I misunderstood. In order to achive your task you have to manipulate your query in a way so that it will look somthing like this

Select * From tbl_User Where FirstName in ('John','Smith') or LastName in ('John','Smith')

If you think you can achive this task easily from your stored procedure well and good, otherwise you can send the whole query from your application and execute it from the stored procedure.

Hope now it will help you out.

Thanks and best regards,

|||

Hi,

your idea is nice. but i shall make my sql like this

Select * From tbl_User Where FirstName in ('%John%','%Smith%') or LastName in ('%John%','%Smith%')

because in need a like %

what you suggested i have to pass exact name

i will check and i will let you know.

thanks for your help

|||

Hi Hussain,

Well I have also tried this way but it didn't gave the required result but the query which I mentioned worked.

Thanks and best regards,

|||

Hey Hussain,

It seems like I have sorted out the problem write following query instead of with IN keyword.

Select * From tbl_User Where FirstName + ' ' + LastName LIKE '%John%Smith%'

Hope it will work with you as well. Happy Coding ;)

Thanks and best regards,

|||

Hi,

again your query will returns the rows like the following

John Smith

John William Smith

but it will not return rows that begin with

Smith

William John

William Smith

i am trying to query in one field suppose you have in the firstName Column the following values

1. John Smith

2. Smith

3. William Smith

4. John William

5. Smith Wiliam

6. XYZ John

7. hjkdfjhkjdfhkjfh smith ashdsjakdhjkh

so your query will not returns all the rows that has either John or Smith

anyway i have solved. and this is my solutions

this is the function i have created

Create

FUNCTION [dbo].[udf_SearchEachWord](@.Stringnvarchar(4000),@.Phrasenvarchar(400))

RETURNS

char(1)

AS

BEGINDECLARE @.INDEXINTDECLARE @.SLICEnvarchar(4000)DECLARE @.ITMES_TABLETABLE(ITEMSNVARCHAR(4000))DECLARE @.FOUNDchar(1)

SET @.FOUND='0'-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z-- ERO FIRST TIME IN LOOPSELECT @.INDEX= 1-- following line added 10/06/04 as null-- values cause issuesWHILE @.INDEX!=0BEGIN-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTERSELECT @.INDEX=CHARINDEX(' ',@.STRING)-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLEIF @.INDEX!=0SELECT @.SLICE=LEFT(@.STRING,@.INDEX- 1)ELSESELECT @.SLICE= @.STRING-- PUT THE ITEM INTO THE RESULTS SETINSERTINTO @.ITMES_TABLE(Items)VALUES(@.SLICE)-- CHOP THE ITEM REMOVED OFF THE MAIN STRINGSELECT @.STRING=RIGHT(@.STRING,LEN(@.STRING)- @.INDEX)-- BREAK OUT IF WE ARE DONEIFLEN(@.STRING)= 0BREAKEND

--================================================================================

SELECT @.INDEX= 1-- following line added 10/06/04 as null-- values cause issuesWHILE @.INDEX!=0BEGIN-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTERSELECT @.INDEX=CHARINDEX(' ',@.Phrase)-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLEIF @.INDEX!=0SELECT @.SLICE=LEFT(@.Phrase,@.INDEX- 1)ELSESELECT @.SLICE= @.Phrase-- PUT THE ITEM INTO THE RESULTS SET

IFEXISTS(SELECT ITEMSFROM @.ITMES_TABLEWHERE ITEMSlike'%'+ @.SLICE+'%')beginSET @.FOUND='1'breakend

-- CHOP THE ITEM REMOVED OFF THE MAIN STRINGSELECT @.Phrase=RIGHT(@.Phrase,LEN(@.Phrase)- @.INDEX)-- BREAK OUT IF WE ARE DONEIFLEN(@.Phrase)= 0BREAKEND

RETURN @.FOUND

END

and this is how i am using it

select

au_fnamefrom authorswhere DBO.udf_SearchEachWord(au_fname,'John Smith')='1'

and this is the results

au_fname

-------

william john

smith john

john smith

smith

william smith

smith william

john william smith

john

(8 row(s) affected)