SQL Search Query

  • 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!

  • Look at full-text indexing and searching:

    http://msdn.microsoft.com/en-us/library/ms142571.aspx

    B

  • pls try below code

    select * from table where right(ltrim(rtrim(name,3)))='red'

    or left(ltrim(rtrim(name,3)))='red'

  • 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.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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

    @fuzzy = @fuzzy

    + '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

    @fuzzy = @fuzzy

    + '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