MySQL query for "starts with but may not fully contain"

Is there a way to do a MySQL query for data fields that start with but may not fully contain a given string?

For instance, if I had the following list of data items:

my_table
1. example.com
2. example.com/subpage
3. subdomain.example.com
4. ain.example.com
5. ple.com

I would like to feed

• "example.com/subpage" and return #1, #2
• "example.com" and return #1
• "wexample.com" and return nothing
• "exa" and return nothing
• "subdomain.example.com/subpage" and return #3

Than开发者_C百科ks a lot!

Given:

CREATE TABLE paths ( path VARCHAR(255) NOT NULL );

Searching for "example.com/subpage" would require the following query:

SELECT * FROM paths WHERE INSTR("example.com/subpage", path) = 1;

Just don't try to run it over a large dataset frequently...

Docs: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_instr

Since your test data indicates you don't want character-by-character matching (but something more like component by component), split the input into the components and search on all prefixes.

If you want to return results for example.com but not exam, you are NOT searching for something that "starts with" yuour input. Not sure if the question is wrong or the examples there.

If the examples are correct, you're going to need to do something to identify if your input is a URL or not using pattern matching like regex or at least specify some solid rules around what you want to match. You'll probably need to explain those rules before a correct recommendation can be made too.

It might be as simple as extracting anything before the "/" if there is one or using your application to break up your request to a url component and a path component.

Mode info on regex in mysql

It seems that you want the column value to match the start of your pattern:

SELECT * FROM my_table WHERE 'example.com' LIKE CONCAT(my_table.my_column, '%');

The downside of this is that it isn't going to use any indexes on my_column.