November 17, 2012 at 12:19 pm
hello,
right now on my search page the sql query i am using is similar to:
select * from table where name like '%search%'
and it returns the exact phrases perfectly
however if search = "car red" there are no results
i want it to be able to return "red card" results also
basically i want it to be:
select * from table where name like '%car%' or name like '%red%'
any ideas? thanks!
November 17, 2012 at 4:00 pm
November 19, 2012 at 3:28 am
pls try below code
select * from table where right(ltrim(rtrim(name,3)))='red'
or left(ltrim(rtrim(name,3)))='red'
November 19, 2012 at 4:37 am
duro654321 (11/17/2012)
hello,right now on my search page the sql query i am using is similar to:
select * from table where name like '%search%'
and it returns the exact phrases perfectly
however if search = "car red" there are no results
i want it to be able to return "red card" results also
basically i want it to be:
select * from table where name like '%car%' or name like '%red%'
any ideas? thanks!
Just do what you want to be, that is right T-SQL:
select * from table where name like '%car%' or name like '%red%'
or, if want anything containing "car" and "red" in the order mentioned, you should do this:
select * from table where name like '%car%red%'
or, if want anything containing "car" and "red" in any order, you can do this:
select * from table where CHARINDEX('car',name) > 0 and CHARINDEX('red',name) > 0
But, the problem with all above samples is - performance! You may find it not satisfactory.
Then consider using Full-Text search.
November 20, 2012 at 2:59 pm
duro654321 (11/17/2012)
hello,right now on my search page the sql query i am using is similar to:
select * from table where name like '%search%'
and it returns the exact phrases perfectly
however if search = "car red" there are no results
i want it to be able to return "red card" results also
basically i want it to be:
select * from table where name like '%car%' or name like '%red%'
any ideas? thanks!
To make best use of Full-Text Indexing and Searching you will need a parsing procedure. I did not write the following code and cannot take credit, but this is what I picked up somewhere along the way:
Call the procedure and then use the parsed result as input for a full-text index search.
DECLARE
@tempstring NVARCHAR(4000)
,@ParsedSearchString NVARCHAR(4000)
EXEC dbo.FullTextParseSearchString
'Red Car'
,'NEAR'
,@tempstring OUTPUT
SET @ParsedSearchString = ISNULL(@ParsedSearchString,'') + ' OR (' + @tempstring + ')'
SELECT @ParsedSearchString AS ParsedResult
The output will look like this
OR ("Red Car" OR ("Red" NEAR "Car") OR ("Red*" NEAR "Car*") OR (FORMSOF(INFLECTIONAL,"Red") AND FORMSOF(INFLECTIONAL,"Car")))
The procedure:
CREATE PROCEDURE [dbo].[FullTextParseSearchString]
(
@keywords VARCHAR(500) = NULL
,@proximity VARCHAR(10) = 'NEAR' -- and, or, near
,@parsedstring VARCHAR(500) OUTPUT
)
AS
BEGIN
SET CONCAT_NULL_YIELDS_NULL OFF
SET NOCOUNT ON
DECLARE
@sql VARCHAR(2000)
,@where VARCHAR(1000)
,@i INT -- old posn in string
,@j INT -- match posn in string
,@k INT -- new start posn
,@len INT -- string length
,@search VARCHAR(500)
,@search2 VARCHAR(500)
,@fuzzy VARCHAR(1000)
,@keyword VARCHAR(500)
,@synonyms VARCHAR(255)
,@new_keywords VARCHAR(1000)
,@found INT
SET @keywords = LTRIM(RTRIM(@keywords))
SET @keywords = REPLACE(@keywords,'*',' ')
SET @keywords = REPLACE(@keywords,' AND ',' ')
SET @keywords = REPLACE(@keywords,' OR ',' ')
SET @keywords = REPLACE(@keywords,'"',' ')
SET @keywords = REPLACE(@keywords,'(',' ')
SET @keywords = REPLACE(@keywords,')',' ')
SET @keywords = REPLACE(@keywords,'o''','')
-- mcdonald's ==> mcdonald''s, escape out the ' which is a valid piece of punctuation
SET @keywords = REPLACE(@keywords,'''','''''')
-- fix é type characters
SET @found = CHARINDEX('é',@keywords)
IF @found > 0
IF @found = LEN(@keywords)
SET @keywords = REPLACE(@keywords,'é','')
ELSE
SET @keywords = REPLACE(@keywords,'é ','* ')
-- set proximity type
IF @proximity IS NULL
OR @proximity = ''
SET @proximity = 'NEAR'
SELECT
@i = 1 -- start of the first string
SELECT
@j-2 = 0 -- end of the first string unknown
SELECT
@len = LEN(@keywords)
SELECT
@search = ''
SELECT
@fuzzy = ''
WHILE (@j <= @len)
BEGIN
SELECT
@k = @j-2 + 1 -- New start posn
-- cut up the string into its individual words, split this
-- into its inflectional part and standard FTI search portion
SELECT
@j-2 = CHARINDEX(' ',@keywords,@k)
-- Finish if no more found
IF (@j <= 0)
BEGIN
-- Add the last part of the string
SELECT
@search = @search + '"'
+ LTRIM(RTRIM(SUBSTRING(@keywords,@i,@len - @i + 1)))
+ '*"'
SELECT
@search2 = @search2 + '"'
+ LTRIM(RTRIM(SUBSTRING(@keywords,@i,@len - @i + 1)))
+ '"'
IF NOT SUBSTRING(@keywords,@k,@len - @k + 1)
IN ('of','and','at','a','the','&','it','for')
SELECT
+ 'FORMSOF(INFLECTIONAL,"'
+ LTRIM(RTRIM(SUBSTRING(@keywords,@k,@len - @k + 1)))
+ '")'
BREAK
END
IF (@j < @len)
BEGIN
IF (SUBSTRING(@keywords,@j - 1,1) <> ' ')
BEGIN
SELECT
@search = @search + '"'
+ LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i)))
+ '*" ' + @proximity + ' '
SELECT
@search2 = @search2 + '"'
+ LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i)))
+ '" ' + @proximity + ' '
IF NOT SUBSTRING(@keywords,@i,@j - @i)
IN ('of','and','at','a','the','&','it','for')
SELECT
+ 'FORMSOF(INFLECTIONAL,"'
+ LTRIM(RTRIM(SUBSTRING(@keywords,@i,@j - @i)))
+ '") AND '
SELECT
@i = @j-2 + 1
END
END
END
IF SUBSTRING(@fuzzy,LEN(@fuzzy) - 2,3) = ' OR'
SET @fuzzy = SUBSTRING(@fuzzy,1,LEN(@fuzzy) - 2)
IF SUBSTRING(@fuzzy,LEN(@fuzzy) - 3,4) = ' AND'
SET @fuzzy = SUBSTRING(@fuzzy,1,LEN(@fuzzy) - 3)
-- Example: ("mcdonalds") OR ("mcdonalds*") OR (FORMSOF(INFLECTIONAL,"mcdonalds"))
SELECT
@search = '"' + @keywords + '" OR ' + '(' + @search2
+ ') OR (' + @search + ') OR (' + @fuzzy + ')'
SET @parsedstring = @search
SET CONCAT_NULL_YIELDS_NULL ON
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply