SQL: Finding DB entry that ends a given string

Question

I need to perform a SQL request to extract some data. I'm not sure if it's possible, and if so, I don't know how to do it. I believe an example is better to demonstrate what I'd like to do.

Lets assume a very simple table:

--------------------------
| ID | domain            |
--------------------------
| 1  | example.com       |
--------------------------
| 2  | stackoverflow.com |
--------------------------

I would like to retrieve the entry whose domain ends a specified string.

If user input were www.example.com, what request could I perform so the entry whose domain is example.com would be retrieved?

The string www.example.com is ended by the string example.com, that means I can't use % LIKE SQL construct, because I'm looking for a substring of the predicate.

Here is a potential dirty workaround to make it clearer:

user_input = "www.stackoverflow.com"
for domain in get_all_domains_from_db():
    if user_input.endswith(domain):
        print "It's this one!"

Ps: Let me know if something isn't clear.

Just wondering if you have www.example.com can't you trim out www. and use just example.com as your input. I mean to say is that always the case? — AJP
– AJP, Commented Jul 29, 2013 at 15:55
@AJP Unfortunately it won't always be the case. User input could be static.srv1.example.com or bla.bli.meh.stackoverflow.com; therefore i can't trim safely for sure. — Xaqq
– Xaqq, Commented Jul 29, 2013 at 15:58
Use clientside ,javascript, or serverside,PHP, to remove unwanted chars not SQL — david strachan
– david strachan, Commented Jul 29, 2013 at 16:00
which programming language you're using either java,php,.net? — Meeran
– Meeran, Commented Jul 29, 2013 at 16:23
@davidstrachan , Meeran : Client side is Javascript, server side is Python. Filtering input, either server or client side, could be a decent alternative solution, but I would prefer that the filtering and search happen on the database -- if it is possible. — Xaqq
– Xaqq, Commented Jul 29, 2013 at 17:06

Chamly Idunil · Accepted Answer · 2013-07-29 17:23:22Z

1

this will resolve your problem.

select * from table_name where :userParam like CONCAT('%', domain)

:userparam will be 'www.example.com'

answered Jul 29, 2013 at 17:23

Chamly Idunil

1,9001 gold badge20 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Xaqq Over a year ago

Yeah ! This is exactly what I've been looking for. Thank you :)

tadman · Accepted Answer · 2013-07-29 15:51:09Z

1

Usually you do this as:

SELECT * FROM tablename WHERE domain LIKE '%example.com'

I'd advise you to never fetch and iterate over all the records in the database. That's a technique that only works for trivial amounts of data. Always use the database to do filtering for you.

answered Jul 29, 2013 at 15:51

tadman

212k23 gold badges237 silver badges266 bronze badges

7 Comments

Xaqq Over a year ago

Yep I know, I don't use this code, it's to demonstrate what I wanna do. However, the user input in my case in www.example.com and I want to retrieve the row example.com, which means I cannot have %example.com in my request, because the user input is www.example.com. I'm not sure if I make sense?

tadman Over a year ago

Why not? example.com matches %example.com where % means simply "zero or more characters". See the reference for LIKE.

Xaqq Over a year ago

Because I can't write %example.com in my query. User input is the filter and it is www.example.com, which means I could write something like: SELECT * FROM tablename WHERE domain LIKE '%www.example.com', but this wouldn't match example.com.

tadman Over a year ago

Ah, right, I missed that. That's a problem with your user input then. The best way to handle this is to extract the domain name using the public suffix list if you can find a library that reads that for the language you're using, or just strip off the www. part by default.

Xaqq Over a year ago

Yep, finding a library to handle domain name, and doing this client side would be an alternative solution.

|

PM 77-1 · Accepted Answer · 2013-07-29 16:31:03Z

1

What about the following:

WHERE SUBSTRING_INDEX(domain, '.', -2) = SUBSTRING_INDEX(user_filter, '.', -2)

If your domain field is always just the domain itself (w/o any prefixes), then

WHERE domain = SUBSTRING_INDEX(user_filter, '.', -2)

This approach is shamelessly borrowed from http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_substring-index

answered Jul 29, 2013 at 16:31

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

1 Comment

Xaqq Over a year ago

My domain field in database is always just the domain itself. However, what you suggest is true only if the TLD is not composed. SUBSTRING_INDEX('www.mysql.co.uk', '.', -2); wouldn't work. Thanks anyway :)

Caleth · Accepted Answer · 2013-07-29 16:36:39Z

1

you could split the user input along the .s, and try shorter and shorter sequences.

some pythonlike pseudocode:

user_input = "www.stackoverflow.com"
split_domain = user_input.split('.')
query = 'SELECT * FROM table WHERE domain = ' + '.'.join(split_domain)
while db_returns_no_rows(query) and split_domain:
    del split_domain[0]
    query = 'SELECT * FROM table WHERE domain = ' + '.'.join(split_domain)

answered Jul 29, 2013 at 16:36

Caleth

66k2 gold badges53 silver badges101 bronze badges

1 Comment

Xaqq Over a year ago

While I appreciate you're answer (and would've done something like that if I was in a hurry) this is kinda hacky :P. I'm looking for a cleaner solution, if possible. Thank you!

Collectives™ on Stack Overflow

SQL: Finding DB entry that ends a given string

4 Answers 4

1 Comment

7 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

7 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related