0

Tricky one, and my brain is mush after staring at my screen for about an hour.

I'm trying to query my database to return the first part of a string (domain name eg. http://www.example.com) in the column image_link.

I have managed this for all rows where the image_link contains .com as part of the string... but I need the code to be more versatile, so it searches for the likes of .net and .co.uk too.

Had thought some sort of nested REPLACE might work, but it doesn't make sense when I try to apply it - and I'm stuck.

Query Builder code:

$builder->select("SUBSTRING(image_link, 1, LOCATE('.com', image_link) + 3) AS domain");

Example strings, with desired results:

http://www.example.com/brands/567.jpg // http://www.example.com
https://www.example.org/photo.png     // https://www.example.org
http://example.net/789                // http://example.net

Any help/advice warmly welcomed!

seixwebdev
  • 111
  • 2
  • 4
  • 14
  • Can you provide several inputs along with what the exact output you want is here? – Tim Biegeleisen Mar 04 '21 at 03:51
  • [@Tim Biegeleisen](https://stackoverflow.com/users/1863229/tim-biegeleisen) amended thanks! – seixwebdev Mar 04 '21 at 04:01
  • What is your MySQL version? – Tim Biegeleisen Mar 04 '21 at 04:03
  • [@Tim Biegeleisen](https://stackoverflow.com/users/1863229/tim-biegeleisen) Version information: 4.9.7 – seixwebdev Mar 04 '21 at 04:06
  • Is that your client version or the MySQL version? Please run this query: `SELECT ROW_NUMBER() OVER (ORDER BY 1)`. Does it execute without error? – Tim Biegeleisen Mar 04 '21 at 04:06
  • [@Tim Biegeleisen](https://stackoverflow.com/users/1863229/tim-biegeleisen) You're right, that's phpMyAdmin version. I can't get that code to run – seixwebdev Mar 04 '21 at 04:11
  • There is a regex based solution, but that would require MySQL 8+ version. Without regex, there won't be much you can do here. – Tim Biegeleisen Mar 04 '21 at 04:13
  • [@Tim Biegeleisen](https://stackoverflow.com/users/1863229/tim-biegeleisen) Fingers crossed! Can you direct me to an example of it, and I'll try and figure it out. – seixwebdev Mar 04 '21 at 04:16
  • Read here: https://stackoverflow.com/questions/3809401/what-is-a-good-regular-expression-to-match-a-url ... again, you'll need MySQL 8+ to do this. – Tim Biegeleisen Mar 04 '21 at 04:16
  • [@TimBiegeleisen](https://stackoverflow.com/users/1863229/tim-biegeleisen) Well, that was a fail! Discovered I'm running MySQL Version 5.7. Little bit out of date! Lol. – seixwebdev Mar 04 '21 at 04:32

1 Answers1

0
SELECT ... , 
       SUBSTRING_INDEX(image_link, '/', 3) domain
FROM test;

Or, if protocol may be absent, then

SELECT ... , 
       SUBSTRING_INDEX(image_link, '/', CASE WHEN LOCATE('//', image_link) THEN 3 ELSE 1 END) domain
FROM test;

fiddle

Akina
  • 39,301
  • 5
  • 14
  • 25