How can I use a wildcard in this proc ?

Question

Post reply

How can I use a wildcard in this proc ?

Mark Platts

SSC Veteran

Points: 236
More actions
September 26, 2005 at 5:25 am

#89300

Hi, I have been trying to use the wildcard in the following procedure in order to return results even from a partial word search. Can anyone please help ... I know that the FREETEXTTABLE does not allow the wildcard but I can't even get it to work with the other commands. I would really appreciate any help anyone could give me.

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, COMStock 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(inv.WebDesc,255) AS ProductDesc,
inv.ListPrice,
inv.Hyperlink,
ATP.MD_ATP AS Stock, ATP.ATP As COMStock,
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
COM_ATP AS ATP ON inv.ProductID = ATP.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.MD_ATP, 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

END

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

peterhe SSChampion Points: 11469 More actions · Answer 1

According BOL, FREETEXTATBLE supports wildcard (*) for columns, like the syntax in your SP.

I don't know what you mean it does not allowed.

Mark Platts SSC Veteran Points: 236 More actions · Answer 2

When I put a wildcard in the actual query, i get an error. When I create a new variable to add the wildcard '*' to the string (i have also tried '%' as a wildcard) then execute the variable as the new querystring i get an error ... with a bit of tweaking, I can get no error, but then I will get no results. The only time I get a result is if I put an entire word into the search form. Once I put part of that word in, I get no results at all.

If you can suggest where to place the wildcard in my query, I'll give it a go.

John Kane SSCrazy Points: 2810 More actions · Answer 3

Change FREETEXTTABLE(IN_PROFIL, *, @SearchText) to CONTAINSTABLE(IN_PROFIL, *, @SearchText) as wildcards are ignored with FREETEXTABLE. Note, Boolean (AND / OR), proximity (NEAR), wildcard ("*" asterisk) operators are ignored in a FREETEXT or FREETEXTTABLE. Additionally, "*" asterisk is the correct wildcard symbol for Full Text Search queries.

Regards,

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/

John T. Kane

Mark Platts SSC Veteran Points: 236 More actions · Answer 4

Thanks John, but I have already tried this. It does not work. Let me try to explain what I want the search to do. If for example I type 'ohn' in a search field, I would like it to return the results for John, johnathan etc ... i am not getting that. What I get are results that are made up of a word 'ohn' only. I simply cannot get a wildcard in the seach or at least one that will work.

John Kane SSCrazy Points: 2810 More actions · Answer 5

You're welcome, Mark,

Sorry, I missed you're reference for a "partial word search". Thanks, for the further difintion. However, SQL Server Full-Text Search (FTS) does not provide the type of pattern search that you are looking for. Specificly, searching for 'ohn' will never return the results for John, johnathan etc. SQL FTS only supports the trailing wildcard, for example "John*" to find John, Johnny, Johnathan. Although, I've not tested CONTAINS or CONTAINSTABLE with these proper names wildcard as normally other nouns/verbs such as "dive*" can be used to find Dive, Dove, Diving, Dived, etc.

This is an often requested function that is more like the T-SQL LIKE pattern functionality, however, FTS is a language-specific linguistic word-based search method. For example, would you want to search for "*og" and find log, dog? Both end in og, but linguisticly, neither are related to each other.

Thanks,

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/

John T. Kane

Mark Platts SSC Veteran Points: 236 More actions · Answer 6

Thanks for your reply John. It was much appreciated. To answer your final question; yes, those are exactly the results I am after if I was to search for "*og" . Does this mean that I will have to re-write the procedure or even go another way around it perhaps using ASP?