Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

Sequencies in string

DECLARE @s VARCHAR(100)
 
SET    @s = 'aardddvaaaarrkkkk'
 
-- Highest frequency of same character
SELECT TOPWITH TIES
              [char],
              COUNT(*) AS cnt
FROM          (
                     SELECT SUBSTRING(@s, 1 + Number, 1) [char]
                     FROM   master..spt_values
                     WHERE Number < DATALENGTH(@s)
                           AND type = 'P'
              ) AS q
GROUP BY      [char]
ORDER BY      COUNT(*) DESC
 
-- Longest sequence of same character
SELECT TOPWITH TIES
              [char],
              CASE [seq]
                     WHEN 0 THEN DATALENGTH(@s) - Number
                     ELSE [seq]
              END AS [seq]
FROM          (
                     SELECT SUBSTRING(@s, 1 + Number, 1) [char],
                           Number,
                           PATINDEX('%[^' + SUBSTRING(@s, 1 + Number, 1) + ']%', SUBSTRING(@s, 2 + Number, 8000)) AS [seq]
                     FROM   master..spt_values
                     WHERE Number < DATALENGTH(@s)
                           AND type = 'P'
              ) AS q
ORDER BY      2 DESC
 
 

Print | posted on Monday, October 27, 2008 8:32 AM | Filed Under [ SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET