SQL select query where column LIKE

Question

I have a table with products in domain name, I need to be able to select the correct product based on domain name matching.

For example, if the domain is google.co.uk it should return the product that is Domain Name Purchase (.co.uk) however if the domain is google.uk it should be Domain Name Purchase (.uk)

How can I make sure I select the correct product when they are similar?

I have tried this query:

SELECT * FROM `prices` WHERE 'google.co.uk' like CONCAT(product, '%')

I also tried:

SELECT * FROM `prices` WHERE product LIKE '%google.co.uk%'

The second query works fine. However if the query is searching for a match with .uk, it will also return products matching .co.uk.

Tabular representation of sample data and desired output would be more helpful. — Sateesh Pagolu
– Sateesh Pagolu, Commented Oct 27, 2015 at 23:30
Assume that we never heard of Internet. We just know what strings of characters are. — PM 77-1
– PM 77-1, Commented Oct 27, 2015 at 23:30
so, it would be a string along with something like .co.uk, .uk, .com, .uk.com and many more — charlie
– charlie, Commented Oct 27, 2015 at 23:31

ydoow · Accepted Answer · 2015-10-28 00:02:32Z

1

From my understanding you are trying to get two separate result sets with string look up with co.uk and .uk respectively.

SELECT * FROM `prices` WHERE product LIKE '%.co.uk%'

should give you result matching .co.uk

SELECT * FROM `prices` WHERE product LIKE '%.uk%' and product NOT LIKE '%.co.uk%'

would give you result matching .uk only, with .co.uk excluded.

answered Oct 28, 2015 at 0:02

ydoow

2,9964 gold badges27 silver badges42 bronze badges

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

4 Comments

charlie Over a year ago

correct, however its not always going to be .co.uk and .uk as there are many others. so its going to be based on the full domain name itself (e.g. domain1.com / domain-test.org / domain-123.me.uk) and many more

ydoow Over a year ago

For this you need to set up a more specific rule for string matching. For example whether it is the product always with url extension and country .com and .com.xx. Only with the knowledge on all possible value in the field you can set up a robust rule for the query.

charlie Over a year ago

how about if i add an extra column to my products table which contains the domain TLD (.com/.co.uk etc) and do the lookup on that column?

ydoow Over a year ago

It would be the best if you can break it down into url extension and country extension

Xathras · Accepted Answer · 2015-10-28 00:13:17Z

0

The one suggestion is to careful on doing this and think on your dB design in future

A fully qualified domain name could consist of any text and in any format given certain guidelines you may want to consider splitting up name vs the qualifier e.g. co.uk into separate fields

answered Oct 28, 2015 at 0:13

Xathras

2201 gold badge5 silver badges15 bronze badges

2 Comments

charlie Over a year ago

ok so if i split the domain in the database, how would i then search my products table?

Xathras Over a year ago

Hi Charlie, as per comment from ydoow, they've hit the nail on the head per my comment. A URL is difficult item to do this and you ideally need to split it out per my comment. When I say split I essentially mean into different columns in your database structure going forward.

Collectives™ on Stack Overflow

SQL select query where column LIKE

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related