Extract an HTTP URL address out of a string of text

Question

We need to extract an a HTTPS:// URL out of a string. There may be more than one HTTPS:// URL address in this string.
How would we extract all HTTPS:// URL addresses out of a string using TSQL ?

Answer 1

If you are using SQL 2016 or later

Declare @Sample Table(Id int identity, MyData varchar(200));  
Insert @Sample(MyData) Values  
('yada yada HTTPS://amazon.com more stuff HTTPS://google.com final stuff'),  
('this has no urls'),  
('HTTPS://microsoft.com');  
  
  
Select ID, MyData, value  
From @Sample  
Cross Apply String_Split(MyData, ' ') As s  
Where value like 'HTTPS://%';  

If you are using an earlier SQL version, you need a user defined split function. Various versions are easy to find on the web by googling "SQL split function". But one version would be

CREATE FUNCTION dbo.Split (@DelimitedString nvarchar(max), @Delimiter nvarchar(max))  
RETURNS table  
/* Use Option(MaxRecursion 0) in queries that call this function if   
   there can be more than 99 delimited values in @DelimitedString */  
AS  
RETURN (  
    WITH Pieces (ID, start, stop) AS (  
      SELECT CAST(1 AS bigint), CAST(1 AS bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString) AS bigint)  
      UNION ALL  
      SELECT ID + 1, CAST(stop + DATALENGTH(@Delimiter)/2 As bigint), CAST(CHARINDEX(@Delimiter, @DelimitedString, stop + DATALENGTH(@Delimiter)/2) AS bigint)  
      FROM Pieces  
      WHERE stop > 0  
    )  
    SELECT ID,  
      SUBSTRING(@DelimitedString, start, CASE WHEN stop > 0 THEN stop-start ELSE LEN(@DelimitedString) END) AS Element  
    FROM Pieces  
  )  
GO  
  
Declare @Sample Table(Id int identity, MyData varchar(200));  
Insert @Sample(MyData) Values  
('yada yada HTTPS://amazon.com more stuff HTTPS://google.com final stuff'),  
('this has no urls'),  
('HTTPS://microsoft.com');  
  
Select s.ID, s.MyData, sp.element  
From @Sample s  
Cross Apply dbo.Split(MyData, ' ') As sp  
Where sp.element like 'HTTPS://%';  

Tom

Answer 2

What version of SQL Server? STRING_SPLIT will work in newer SQL versions.

In older versions I'd use a user defined function that returns a table variable. Inside the function I'd use a simple while loop in combination with PATTERN to find the next substring.

Answer 3

Thank you all. There were carriage returns and line feeds and tab in the table which was a "text" data type, so those all had to be cleared out first.
Thanks so much!