May 5, 2008 at 2:40 pm
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
May 5, 2008 at 3:18 pm
[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]
May 5, 2008 at 3:42 pm
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]
May 5, 2008 at 4:00 pm
Thanks rbarryyoung
Just the key!
Just using % between words !
Thanks
May 6, 2008 at 11:07 pm
@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.
May 6, 2008 at 11:25 pm
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]
May 7, 2008 at 1:14 am
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.
May 8, 2008 at 11:39 am
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/
May 8, 2008 at 1:07 pm
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
May 8, 2008 at 1:25 pm
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.
May 8, 2008 at 1:57 pm
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]
May 8, 2008 at 2:25 pm
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.
May 8, 2008 at 4:08 pm
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]
May 9, 2008 at 7:12 am
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.
May 10, 2008 at 12:10 am
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