How to use LIKE for 2 or more words

  • Hello i need to know hoy to use the LIKE operator to find results that contains 2 or more words.

    ================TABLE EXAMPLE======================

    I HAVE A TABLE CALLED ITEMS

    ITEMNAME

    Good Bike

    Good Mountain Bike

    Klein Bike Mountain

    ===================================================

    If i use SELECT ITEMNAME FROM ITEMS WHERE ITEMNAME LIKE '%Good Bike%' i only get:

    Good Bike

    What to code i need to write if i want to get that results for QUERY: "Good Bike" that will return

    Good Bike

    Good Mountain Bike

    NOTE: the phrase "Good Bike" come from a variable

    @StringThatUsersUseToFind

  • [font="Arial"]

    Hello,

    There is a wild card character which is the percent sign. ie %

    select field

    from table

    where field like '%bike%'

    order by field.

    If you don't put a % sign in front then it will find records that start with what ever you specifiy.

    select field

    from table

    where field like 'Bike%'

    This means that only records whose field start with the letters Bike and then are followed by any other characters will be selected.

    Also you can use special characters for partial work searches.

    I hope this helps.

    Terry

    [/font]

  • Try something like this:

    SELECT ITEMNAME

    FROM ITEMS

    WHERE ITEMNAME LIKE '%' + Replace(@StringThatUsersUseToFind, ' ', ' % ') + '%'

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Thanks rbarryyoung

    Just the key!

    Just using % between words !

    Thanks

  • @RBarryYoung: Perfect! I did not know this works until now (we always use a kind of fulltext indexing for this).

    The only drawback for this syntax is that you have to know the word order. If you would like it to search for words in an unordered manner, you still have to use some kind of fulltext indexing. Perhaps you have some luminant idea I did not notice yet for that too 🙂 ?

    [font="Courier New"]
    ------------------------
    MCPD Web Developer, MCPD Windows Developer
    [/font]

    Computers are made to solve problems you did not have in the days they didn't exist.

  • Well, you can always use an OR:

    SELECT ITEMNAME

    FROM ITEMS

    WHERE ITEMNAME LIKE '%Good%'

    Or ITEMNAME LIKE '%Bike%'

    To do this with a parameter, you would have to split it on the space.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Of course, but that will bless you with a serious performance issue when talking about > 500,000 rows (as there are in our table) 😉 . I did not find a faster way than fulltext indexing yet, I just wondered if you should come with something blowing my mind :):):) .

    [font="Courier New"]
    ------------------------
    MCPD Web Developer, MCPD Windows Developer
    [/font]

    Computers are made to solve problems you did not have in the days they didn't exist.

  • My strong recommendation is to use full-text (CONTAINS) vs. LIKE clause pattern matching for tons of reasons:

    1. Performance!

    2. You won't get false positives. E.g., "%good%" will also find "goodness", "goodnight", etc.

    3. You get "stemming" (plurals, etc.) E.g., if the user entered "%bikes% it will not find "bike" but under full-text with stemming as part of the query it will.

    4. word order doesn't matter

    5. You can mix "AND" and "OR"

    6. and many, many, more.

    Bit the bullet now vs. trying to hack something.

    Just read the BOL or better yet, Hillary Cotter's articles (parts 1 and 2) at: http://www.simple-talk.com/sql/learn-sql-server/sql-server-full-text-search-language-features/


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • As mentioned in http://www.simple-talk.com/sql/learn-sql...

    in order to perform full text searching on a table you need to:

    1) ..

    2) Create a full text catalog in which to store full text indexes for a given table

    But the database im development will insert arround of 10.000 new rows per day.

    I don't have knowledgements of full-text indexing, but we can't refill every day the Full Text Catalog .. i think so

  • But the database im development will insert arround of 10.000 new rows per day.

    I don't have knowledgements of full-text indexing, but we can't refill every day the Full Text Catalog .. i think so

    Given the number of rows being added to the table every day you're talking about 300000 rows per month, 3600000 per year. That is a LOT of rows to be scanning using a LIKE clause! Therefore, I strongly suggest that you look at full-text.

    As for indexing the new rows, it will happen automatically provided that you have set up the catalog correctly. See "start_change_tracking" and "start_background_updateindex" for system procedure sp_fulltext_table in BOL. Also note that there is newer syntax for SQL Server 2005.

    Then as rows are added or modified they are automatically indexed in the background.


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • Fulltext indexing is a substantial administrative commitiment and should not be turned on without evaluating its costs and impacts.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Fulltext indexing is a substantial administrative commitiment

    Interesting comment. We're running a SaaS business using it to index plain text as well as binary documents (Word, PowerPoint, PDF, etc.) with NO additional administrative overhead.

    should not be turned on without evaluating its costs and impacts.

    True. The evaluation would, of course, be part of any proper system design. Along with a prototype evaluating both LIKE clauses and Full-Text against a proper set of data to determine the feature/functionality behavior of the application as well as performance.


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • JohnG (5/8/2008)


    Fulltext indexing is a substantial administrative commitiment

    Interesting comment. We're running a SaaS business using it to index plain text as well as binary documents (Word, PowerPoint, PDF, etc.) with NO additional administrative overhead.

    Its suprising to hear that you use Full-Text indexing but have not been administering it and have no commitment to administer it (which is what I was talking about).

    Most of my SQL Server customers who do proper system design would recognize that a database administrator should install it and create the catalog. And they would realize that this requires diskspace, which has to be allocated and planned for, and it consumes disk bandwidth which also has to be allocated and planned for, and it has to be backed-up and they have to insure that the backup target devices have and continue to have the additional space for these additional backups, and that if they have to move, migrate, patch or upgrade SQL Server, that full-text indexing is an additional consideration for that as well. And all of these things are "additional administrative committments", and I strongly recommend that anyone using Full-Text indexing do these things, because it really is part of "proper system administration".

    And, of the solutions discussed, and most other possible solutions, Full-text indexing is unique in this. Deciding to use LIKE vs. "=" vs. RegEx, or using LIKE in a different way, or adding or removing an Index does not require this type of committment. And I mentioned this for precisely this reason and because I have seen many a SQL Server that was seriously hosed by someone who turned it on without realizing this or taking it into account.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Its suprising to hear that you use Full-Text indexing but have not been administering it and have no commitment to administer it (which is what I was talking about).

    Most of my SQL Server customers who do proper system design would recognize that a database administrator should install it and create the catalog. And they would realize that this requires diskspace, which has to be allocated and planned for, and it consumes disk bandwidth which also has to be allocated and planned for, and it has to be backed-up and they have to insure that the backup target devices have and continue to have the additional space for these additional backups, and that if they have to move, migrate, patch or upgrade SQL Server, that full-text indexing is an additional consideration for that as well. And all of these things are "additional administrative committments", and I strongly recommend that anyone using Full-Text indexing do these things, because it really is part of "proper system administration".

    A misunderstanding. We (I) do all of the above as it is part of the overall application, system and database design. Even a minor change in the application, independent of full-text, can surface all of the issues that you bring up. We don't throw half-baked stuff "over the wall" to the DBA (where "A" = administrator) to make it work. I'm the DBA where "A" = Architect with a holistic view of all facets of a computer system. We never just "turn something on" and expect the DBAs to handle it. Therefore, in our environment, our "administrators" only have to do routine administration tasks, like setting up a new database for a new customer, etc.

    P.S. Your comments (install it, properly back up the catalog, etc.) would indicate SQL Server 2000. With SQL Server 2005 it is properly bundled therefore minimizing the "administration". Reportedly, in SQL Server 2008 all of this is integrated further within the database itself, thus making it even more transparent, and further reducing the "administration", as you state, costs.


    [font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

  • rbarryyoung (5/5/2008)


    Try something like this:

    SELECT ITEMNAME

    FROM ITEMS

    WHERE ITEMNAME LIKE '%' + Replace(@StringThatUsersUseToFind, ' ', ' % ') + '%'

    rbarryyoung,

    This may yield incorrect results depending on the spaces in the string. In the example, posted the correct results are not returned because the said query is looking for string as follows: %Good % Bike %. The problem with this is if the word Bike does not have a space following it in the column, it is not returned. The query should completely replace the space with a percent sign and contain 0 spaces as such.

    LIKE '%' + Replace('Good Bike', ' ', '%') + '%'

    I would also like to add that the potential impact to using the like statement is going to be devestating. A like contains almost ALWAYS gaurentees a table scan. The OP said that he has aprox 500,000 rows and growing. This would be one heck of a table scan.

    Full text searching, may add administrative overhead on the forefront, but on the backend it provides better performance, greater user satisfaction, flexiblity, and scalability. All of these things equate to greater ROI. I believe this is a prime example of when to use the full text engine.

Viewing 15 posts - 1 through 15 (of 16 total)

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