stephencushen
asked on
Match exact match partial phrase SQL
hi
i want to do the following
|-------------------------
Enter Phrase |___________________| o Match Exact
i have a textbox and a checkbox on a webpage, i want to the user to be a able to enter a search a description field in a database table,
if they enter 'redhat linux', and dont click match exact, i want rows to be returned if eiter 'redhat' or 'linux' or 'redhat linux' appear in the description field, and if they check the match exact field then only rows with 'redhat linux' should be returned,
is there an easy way to do this in sql, or do i have to split the search string then do seperate searches, then combine the search results then remove duplicate rows??
also if i do this 'WHERE description LIKE %linux%' will this bring back records where linux is the first word in the description, or would i have to do seperate sql searchs for %linux%, linux%, and %linux,
i want to do the following
|-------------------------
Enter Phrase |___________________| o Match Exact
i have a textbox and a checkbox on a webpage, i want to the user to be a able to enter a search a description field in a database table,
if they enter 'redhat linux', and dont click match exact, i want rows to be returned if eiter 'redhat' or 'linux' or 'redhat linux' appear in the description field, and if they check the match exact field then only rows with 'redhat linux' should be returned,
is there an easy way to do this in sql, or do i have to split the search string then do seperate searches, then combine the search results then remove duplicate rows??
also if i do this 'WHERE description LIKE %linux%' will this bring back records where linux is the first word in the description, or would i have to do seperate sql searchs for %linux%, linux%, and %linux,
I *think* you have to separate out the various components, because if you did
Like '%redhat%linux%'
you'd only get those records that had those words, in that order, somewhere in the search field.
So what you really want is
Like '%linux%' OR Like '%redhat%'
I don't know how you'd do that in one (relatively simple) SQL statement.
If you do
Like '%linux%'
you'll get anything with 'linux' in it anywhere, beginning, middle, or end.
Like '%redhat%linux%'
you'd only get those records that had those words, in that order, somewhere in the search field.
So what you really want is
Like '%linux%' OR Like '%redhat%'
I don't know how you'd do that in one (relatively simple) SQL statement.
If you do
Like '%linux%'
you'll get anything with 'linux' in it anywhere, beginning, middle, or end.
'linux%' begin
'%_linux_%' middle
'%linux' end
'%_linux_%' middle
'%linux' end
create proc getBySearched
@phrase nvarchar(3000),
@exact int= 0
AS
declare @text nvarchar(4000)
set @text= ' select * from w2string where val like ''' + replace ( ' '+ @phrase+' ', ' ', case @exact when 1 then ' ' else '%' end) +''''
--print @text
exec ( @text)
@phrase nvarchar(3000),
@exact int= 0
AS
declare @text nvarchar(4000)
set @text= ' select * from w2string where val like ''' + replace ( ' '+ @phrase+' ', ' ', case @exact when 1 then ' ' else '%' end) +''''
--print @text
exec ( @text)
-- sorry, posted with my test tables. replace mytable with your table name
create proc getBySearched
@phrase nvarchar(3000),
@exact int= 0
AS
declare @text nvarchar(4000)
set @text= ' select * from myTable where description like ''' + replace ( ' '+ @phrase+' ', ' ', case @exact when 1 then ' ' else '%' end) +''''
--print @text
exec ( @text)
create proc getBySearched
@phrase nvarchar(3000),
@exact int= 0
AS
declare @text nvarchar(4000)
set @text= ' select * from myTable where description like ''' + replace ( ' '+ @phrase+' ', ' ', case @exact when 1 then ' ' else '%' end) +''''
--print @text
exec ( @text)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
full-text serching seems to be the way to go, but i cant use it till i get mssql server, im using msde for development
ASKER