sql query search removing end of string

Question

I am trying to search domain names ending in particular keywords. e.g. "car" would bring up buycar.com, but not carbuy.com.

So if my query is

SELECT * FROM domains WHERE LIKE '%car'

Will not show any results at all, obviously because the domains dont end in car, they end in .com, or .co, or something.

I think I need to do some sort of regex replace to search the domain, until the first .

Or whatever would do the equivalent of this in sql for php:

$pos = strpos($domain,'.');
$search = substr($domain,0,$pos);

So it would just search the actual domain without the TLD. Is this possible with sql?

Gordon Linoff · Accepted Answer · 2017-09-06 23:30:25Z

4

How about using:

SELECT *
FROM domains
WHERE domain LIKE '%car.%'

answered Sep 6, 2017 at 23:30

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

I only used "car" as an example, it could be anything

@Source . . . Then you would replace "car" with whatever you want to search.

sa-es-ir · Accepted Answer · 2017-09-07 04:37:32Z

0

You could remove characters like .com, .IR, .co after car and run own query.

Please try this:

SELECT *
FROM domains
WHERE 
REVERSE(SUBSTRING(REVERSE(domain),CHARINDEX('.',REVERSE(domain))+1,LEN(domain 
))) LIKE '%car'

if you don't remove that character and use Like '%car.%' maybe get some thing like this: car.site.com

answered Sep 7, 2017 at 1:48

sa-es-ir

5,1902 gold badges20 silver badges38 bronze badges