SET NOCOUNT ON; DECLARE @Body nvarchar(max), @Length int...

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

SET NOCOUNT ON;

DECLARE
  @Body nvarchar(max),
  @Length integer,
  @Find nvarchar(4000) = N'http://connect.microsoft.com/SQLServer/',
  @EndMarker nvarchar(50) = N'%[^-A-Za-z0-9/.?=:]%',
  @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;

  SET @Length = LEN(@Body);

  WHILE @Pos > 0
  BEGIN
    SET @Body = SUBSTRING(@Body, @Pos, @Length);
    SET @End = PATINDEX(@EndMarker, @Body) - 1;
    
    IF @End = 0
    BEGIN
      SELECT @Body AS [End Marker Not Found];
      BREAK;
    END;
    
    INSERT @Results (url) VALUES (LEFT(@Body, @End));
    SET @Count += 1;
    
    SET @Body = SUBSTRING(@Body, @End, @Length);
    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