MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (image_link VARCHAR(255), desired_output VARCHAR(255));
INSERT INTO test VALUES
('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');

Records: 3  Duplicates: 0  Warnings: 0


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


image_link
desired_output
domain


http://www.example.com/brands/567.jpg
http://www.example.com
http://www.example.com

https://www.example.org/photo.png
https://www.example.org
https://www.example.org

http://example.net/789
http://example.net
http://example.net


INSERT INTO test VALUES
('www.example.com/brands/567.jpg', 'www.example.com');
SELECT *, SUBSTRING_INDEX(image_link, '/', 3) domain
FROM test;

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


image_link
desired_output
domain


http://www.example.com/brands/567.jpg
http://www.example.com
http://www.example.com

https://www.example.org/photo.png
https://www.example.org
https://www.example.org

http://example.net/789
http://example.net
http://example.net

www.example.com/brands/567.jpg
www.example.com
www.example.com/brands/567.jpg




image_link
desired_output
domain


http://www.example.com/brands/567.jpg
http://www.example.com
http://www.example.com

https://www.example.org/photo.png
https://www.example.org
https://www.example.org

http://example.net/789
http://example.net
http://example.net

www.example.com/brands/567.jpg
www.example.com
www.example.com

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