0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for software engineers working on automated testing, continuous delivery, service integration and monitoring, and building SDLC infrastructure

SELECT PostId, 

/* we want to sort by this, so let's make it an integer */
CAST(
SUBSTRING(
  /* search full post text */
  Text, 
  /* get the part starting after http://superuser.com/questions/ */
  CHARINDEX(
    /* by finding the offset of that URL part */
    'http://superuser.com/questions/', 
    /* in Text */
    Text
  )
  /* and adding its length */
  + LEN('http://superuser.com/questions/'),
  
  /* and get a number of characters from that offset */
  CHARINDEX(
    /* search for the first forward slash */
    '/', 
    Text,
    
    /* this is the offset again */
    CHARINDEX(
      'http://superuser.com/questions/', 
      Text
    ) + LEN('http://superuser.com/questions/')
    /* offset end */
  ) -  CHARINDEX(
    /* since only the search skips the first part, 
    but the return value is still based on the full text, 
    we need to remove the characters we 
    skipped when searching */
      'http://superuser.com/questions/', 
      Text
    ) - LEN('http://superuser.com/questions/')
    ) AS Int) OriginalPost
FROM PostHistory 

/* Community user */
WHERE UserId = -1

/* this has always been the notice */
AND Comment = 'insert duplicate link'

/* now order by OriginalPost */
ORDER BY OriginalPost

fork query

edited jun 15 12

created jun 15 12 Daniel Beck