nicker nicker - 5 months ago 10
PHP Question

How to select distinct a substring field or remove duplicate field

Sample Data:

[{url: bing.com},
{url: bing.com/search?q=test},
{url: rt.com/test}]


Currently what I do is return a filtered url that removed anything after '/'

SELECT url,
CASE WHEN LOCATE('/', url) THEN LEFT(url, LOCATE('/', url) - 1)
ELSE url END AS editedUrl
FROM testTable;


The output result will now be

url[0] = bing.com
url[1] = bing.com
url[2] = rt.com


what I want to do is remove duplicate now, which means it will only return
url[0]
and
url[2]
, I tried doing this

SELECT DISTINCT url,
CASE WHEN LOCATE('/', url) THEN LEFT(url, LOCATE('/', url) - 1)
ELSE url END AS editedUrl
FROM testTable;


However it will compare url base on their actual value rather than after filtered value.

Please assist,
Thanks.

Answer

You must group by the edited URL:

SELECT MIN(url), 
   CASE
      WHEN LOCATE('/', url) THEN LEFT(url, LOCATE('/', url) - 1)
      ELSE url
   END AS editedUrl
FROM testTable
GROUP BY editedUrl

If you just want a unique editedUrl:

SELECT DISTINCT 
   CASE
      WHEN LOCATE('/', url) THEN LEFT(url, LOCATE('/', url) - 1)
      ELSE url
   END AS editedUrl
FROM testTable