April 5, 2012 at 7:00 am
No problem 🙂
As mentioned before I would seriously look into the full text search option.
I would also not run this on large tables either as if a row has 30 different strings the CROSS APPLY will return 30 seperate rows for that propertyid so your dataset could quickly become very large..
Hopefully someone will post a more efficient solution if not at least you have something..:-D
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 5, 2012 at 7:37 am
John Mitchell-245523 (4/5/2012)
Those decimals at the ends of the rows don't convert into datetime values, I'm afraid. But seriously, I really think full-text indexing is the way to go on this. Why roll your own solution when someone has already built one for you?John
Well John, that is why I came here in the first place. Andy' solution seems to work so far but I'll still look into the text indexing of sql server. I thank you guys very much for all your help. I am happier now and more knowledgeable than this morning. :hehe::-D
April 5, 2012 at 7:48 am
Slight amendment to the SQL:
DECLARE
@SearchString VARCHAR (8000)
SET
@SearchString = 'WAREHOUSE MOSHALASHI NEW IPAJA'
SELECT
AGT.Name
,AGT.Mobile1
,P.Description
,P.Price
,L.Name
FROM
dbo.rhc_Properties AS P
LEFT JOIN dbo.rhc_Agents AS AGT
ON P.AgentID = AGT.AgentID
LEFT JOIN dbo.rhc_Areas AS A
ON P.AreaID = A.AreaID
LEFT JOIN dbo.rhc_Locations AS L
ON P.LocationID = L.LocationID
INNER JOIN (SELECT
P2.PropertyID
,String.Item
FROM
dbo.rhc_Properties AS P2
CROSS APPLY dbo.DelimitedSplit8K(P2.Description, ' ') String
CROSS APPLY dbo.DelimitedSplit8K(@SearchString, ' ') SearchString
WHERE
String.Item LIKE '%'+ SearchString.Item +'%'
) AS SearchString ON P.PropertyID = SearchString.PropertyID
The previous code would not pick up a property if it included special characters without spaces after the string i.e. WAREHOUSE, OR WAREHOUSE.
This should rectify that
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 5, 2012 at 8:02 am
Full-text is probably the way to go, but short of that I'd probably split the string like Andy has suggested, but I'd put it in a temporary table, then I'd probably break the search down by permanent table. Like:
INsert INto #areas
Select * from areas AS A Where exists (Select 1 from #search_args as SA WHERE A.description LIke '%' + SA.word + '%'
Other tables
Then query the full tables using the temporary tables.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 11, 2012 at 11:45 am
Oh, gosh, what if someone types in "2br in erith" or "2bd in erith" or "two bedroom in erith" or "2rm in eriht" (note misspelled word)? You can't fix stupid and you can't expect much with free-text entry. Turn it around and make the users enter the search criteria in separate (optional), promptable fields ("Number of bedrooms", "Location", etc.), then do some dynamic SQL magic to return the results.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply