How to use SQL LIKE

Question

Hello, I am trying to filter results in an sql query using the like statement. Here are the results

/q/my_rat_terrior_is_about_8_just_this_moring_hes_barley_moving_around_panting_heavy_and_shaking_like_shivering/1
/addquestion
/addquestion/Validation
/q/how_do_you_get_a_hamster_out_of_a_wall/2

These are urls that are stored in my database. I want to match urls like these

/q/how_do_you_get_a_hamster_out_of_a_wall/2
/q/my_rat_terrior_is_about_8_just_this_moring_hes_barley_moving_around_panting_heavy_and_shaking_like_shivering/1

This is what I have tried:

select * from MyURLs where MYuri LIKE '/q/'

But its not returning any results. Any Ideas on this?

Answer 1

You need to use % as a wildcard:

select * from MyURLs where MYuri LIKE '%/q/%'

That would return records with a '/q/' anywhere in the field. If you want it to start with '/q/' but have anything after that, you would use:

select * from MyURLs where MYuri LIKE '/q/%'

Answer 2

Kaleb has already given you the solution (and Joel has warned about index performance in the comment) but I'd like to suggest another route, one that will scale well. If your tables are never going to get to be very large, you can ignore this advice. In fact, you can ignore it right up until the time when you expect performance will suffer (or performance has actually started suffering) - this is the YAGNI principle.

Databases are almost always read far more than they're written. This means that the right time to figure out when the URL starts with, or contains, a "/q/" is when the data is put in the table, not every single time you extract it. This amortizes the cost of the calculation (done at write time) across all the reads.

For this scalable solution, you should have a totally separate column in the table, something like "startsWithQ" or "hasQ".

Then, with an insert/update trigger, set that column based on the URL being put into the table.

Then your query will become:

select * from MyURLs where hasQ = 'YES'

and, provided you have an index on that column, your queries will scream along.

If you actually look at most database problems, they're usually "my query isn't fast enough" rather than "my disk isn't big enough". Trading disk space for speed is usually the best approach.

This may provide a performance improvent if your search string is '/q/%'. This should be profiled, regularly and in production - databases are rarely set-and-forget since performance can change based on the data in the tables - measure, don't guess.

It will certainly provide an improvement if the search string is '%/q/%', unless your database is one of those very rare beasts that has more writes than reads.

Answer 3

If you have a lot text like this, create a full-text catalog, create and populate a full-text index, then query full-text data. its key words: contains, freetext, containstable, freetexttable. sql server 2005 and 2008 have powerful free-text search.