How can I add a wildcard to this search
-
I have to include a wildcard in the following search. The search criteria is obtained from an ASP page and the results fed back. As it is, I can only get an exact match. (search string in red)
CREATE FUNCTION fn_ProductSearchFT (@SearchText nvarchar(255)
)
RETURNS @ProductSearchFT TABLE (
ProductID int,
-- MAX(pl.WebCatID) AS CategoryID,
ProductDesc nvarchar(255),
ListPrice smallmoney,
Hyperlink nvarchar(255),
Stock int,
Discontinued bit,
PriceList bit,
Recommended bit,
NONstockable bit,
ImageFilename nvarchar(50),
RANK int
)
AS
BEGIN
-- Match records with the search phrase entered
INSERT @ProductSearchFT
SELECT DISTINCT inv.ProductID,
-- MAX(pl.WebCatID) AS CategoryID,
left(WebDesc,255) AS ProductDesc,
inv.ListPrice,
inv.Hyperlink,
ATP.ATP AS Stock,
inv.Discontinued,
inv.PriceList,
inv.Recommended,
NONstockable,
inv.ImageFilename,
ft_inv.RANK
FROM
(IN_PROFIL AS inv INNER JOIN
FREETEXTTABLE(IN_PROFIL, *, @SearchText ) AS ft_inv
ON inv.ProductID = ft_inv.)
LEFT JOIN
ATP ON ATP.ITEM_ID = inv.ProductID INNER JOIN w_tblProductListing as
tpl ON inv.ProductID = tpl.ProductID
GROUP BY
inv.ProductID,
-- pl.WebCatID,
inv.WebDesc,
inv.ListPrice,
inv.Hyperlink,
ATP.ATP,
inv.Discontinued,
inv.PriceList,
inv.Recommended,
NONstockable,
inv.ImageFilename,
tpl.WebCatID,
ft_inv.RANK
HAVING inv.Discontinued=0
AND inv.PriceList=1
AND tpl.WebCatID <> NULL
ORDER BY ft_inv.RANK DESC
RETURN
I have no idea why the developer had to do it this way, but it is quite a problem. I need to be able to enter a word or number and any prod that contains that sequence will be returned in the list of results.M -
how about building the query:
DECLARE @Query varchar(8000)
Set @Query = ' SELECT DISTINCT inv.ProductID,
-- MAX(pl.WebCatID) AS CategoryID,
left(WebDesc,255) AS ProductDesc,
inv.ListPrice,
inv.Hyperlink,
ATP.ATP AS Stock,
inv.Discontinued,
inv.PriceList,
inv.Recommended,
NONstockable,
inv.ImageFilename,
ft_inv.RANK
FROM
(IN_PROFIL AS inv INNER JOIN
FREETEXTTABLE(IN_PROFIL, *, ' + @SearchText + ' ) AS ft_inv
ON inv.ProductID = ft_inv.)
LEFT JOIN
ATP ON ATP.ITEM_ID = inv.ProductID INNER JOIN w_tblProductListing as
tpl ON inv.ProductID = tpl.ProductID
GROUP BY
inv.ProductID,
-- pl.WebCatID,
inv.WebDesc,
inv.ListPrice,
inv.Hyperlink,
ATP.ATP,
inv.Discontinued,
inv.PriceList,
inv.Recommended,
NONstockable,
inv.ImageFilename,
tpl.WebCatID,
ft_inv.RANK
HAVING inv.Discontinued=0
AND inv.PriceList=1
AND tpl.WebCatID <> NULL
ORDER BY ft_inv.RANK DESC'
exec(@Query)
Everett Wilson
ewilson10@yahoo.com -
Before insert:
set @searchtext='%'+@searchtext +'%'
Bill
-
Sorry, but it did not work ... neither did the previous one. Can you suggest an alternative way to get the wildcard added to the search?
-
This works:
use northwind
declare @searchtext varchar(10)
set @searchtext='the'
set @searchtext='%'+@searchtext+'%'
select * from customers
where companyname like @searchtext
... but I don't have full text set up to test with freetexttable. After looking at freetexttable again in BOL, it may be a syntax issue with freetexttable.
Can you do a simple example with freetexttable using a literal like
'%the%' as the search text? In other words, will freeetexttable even take wildcards?
-
(edited : Never mind. I didn't know FREETEXTTABLE was a full text search related function.
)The problem isn't with that function. The actual problem is within the FREETEXTTABLE function. Show us that function and we could probably tell you what to change. Alternatively, if you look at it yourself you may figure it out especially with Bill Nye the science guy's suggestion.
-
Searching text with Full-text Search (FTS) can be senstive to both the OS version (Win2K vs. Win2003 or WinXP) as well as to the actual text you are searching for, depending upon if any punctuation characters are in contact with the search word. Could you post the full output of:
select @@version
Also, you may want to change FREETEXTTABLE to CONTAINSTABLE as FREETEXT* ignores wildcards and boolen operators. Also, what is the exact input string that you are passing to your @SearchText variable?
You may find the following stored procedure useful:
use pubs
-- DROP PROCEDURE sp_FTSearchPubsInfo
go
CREATE PROCEDURE sp_FTSearchPubsInfo ( @vcSearchText varchar(7800))
AS
declare @s-2 as varchar (8000)
set @s-2='select pub_id, pr_info from pub_info where contains(pr_info,'+''''+@vcSearchText+''''+')'
exec (@s)
go
EXEC sp_FTSearchPubsInfo '("book*") or ("publish*")'
-- or
EXEC sp_FTSearchPubsInfo '(("book" or "books") and ("publish" or "publisher"))'
go
/* -- returns:
pub_id pr_info
------ --------------------------------------------------
9952 This is sample text data for Scootney Books, publi.....
0736 This is sample text data for New Moon Books, publi.....
(2 row(s) affected)
*/
Regards,
John T. Kane
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply