?
Solved

Match exact match partial phrase SQL

Posted on 2004-11-11
7
Medium Priority
?
494 Views
Last Modified: 2012-06-21
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,
0
Comment
Question by:stephencushen
7 Comments
 

Author Comment

by:stephencushen
ID: 12554830
thanks in advance
0
 
LVL 12

Expert Comment

by:pique_tech
ID: 12554883
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.

0
 
LVL 6

Expert Comment

by:OlegP
ID: 12555206
'linux%'    begin
'%_linux_%' middle
'%linux' end
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 12

Expert Comment

by:ill
ID: 12555644
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)
0
 
LVL 12

Expert Comment

by:ill
ID: 12555658
-- 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)
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 525 total points
ID: 12560742
I strongly urge you to take a look at SQL Server's Full-Text search capabilities.  It looks like you are trying to re-invent the wheel.
0
 

Author Comment

by:stephencushen
ID: 12670801
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
 
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question