May be this will help you.
SELECT SUBSTRING(myColumn, 1, CASE CHARINDEX("https://stackoverflow.com/", myColumn)
WHEN 0
THEN LEN(myColumn)
ELSE CHARINDEX("https://stackoverflow.com/", myColumn) - 1
END) AS FirstName
,SUBSTRING(myColumn, CASE CHARINDEX("https://stackoverflow.com/", myColumn)
WHEN 0
THEN LEN(myColumn) + 1
ELSE CHARINDEX("https://stackoverflow.com/", myColumn) + 1
END, 1000) AS LastName
FROM MyTable