MySQL query to find all URLs that are substring of other URLs

I have a MySQL 开发者_JS百科field URL of type Varchar.

Is it possible with one MySQL query to return all records where the string value URL is a substring of another record URL value, and display both the substrings and full strings records?

---

You join the table with itself. Something like:

SELECT t1.URL, t2.URL AS SUBURL
FROM mytable t1, mytable t2
WHERE INSTR(t1.URL, t2.URL) > 0
AND t1.ID < t2.ID

Note that the query has N^2 complexity and string matching is slow. Expect terrible performance on large tables.

---

In general, the answer is "Yes", using the INSTR() or LOCATE() functions. But it depends on how your URLS are stored. Things like "http://", subdomains, and such can give you many false positives or false negatives. Not false in the sense that INSTR() is broken, but false in the sense that it won't do what you might want it to.