DECLARE @Body nvarchar(max), @Find nvarchar(4000) = N'htt...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE
  @Body nvarchar(max),
  @Find nvarchar(4000) = N'https://connect.microsoft.com/SQLServer/',
  @Count integer = 0,
  @Pos integer, 
  @End integer;

DECLARE c CURSOR LOCAL FORWARD_ONLY DYNAMIC READ_ONLY FOR
  SELECT V.pos, P.Body
  FROM dbo.Posts AS P 
  CROSS APPLY (VALUES(CHARINDEX(@Find, P.Body COLLATE Latin1_General_100_BIN2))) AS V (pos)
  WHERE V.pos > 0;
  
DECLARE @Results table (url nvarchar(max) NOT NULL);

OPEN c;

WHILE @Count < 20
BEGIN
  FETCH c INTO @Pos, @Body;

  IF @@FETCH_STATUS <> 0
  BEGIN
      IF @@FETCH_STATUS = -1 BREAK;
      IF @@FETCH_STATUS = -2 CONTINUE;
  END;

  WHILE @Pos > 0
  BEGIN
    SET @End = CHARINDEX('"', @Body, @Pos);
    IF @End = 0 BREAK;
    
    INSERT @Results (url) VALUES (SUBSTRING(@Body, @Pos, @End - @Pos));
    SET @Count += 1;
    
    SET @Body = SUBSTRING(@Body, @End + 1, LEN(@Body));
    SET @Pos = CHARINDEX(@Find, @Body);
  END;
END;

CLOSE c; DEALLOCATE c;

SELECT * FROM @Results;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results