TSQL Challenge 70 - Find the longest sequence of alphabets in a string

• jacob sebastian

  SSChampion

  Points: 11822

  More actions

  November 27, 2011 at 8:56 am

  #146634

  Comments posted to this topic are about the item TSQL Challenge 70 - Find the longest sequence of alphabets in a string

  .

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  November 28, 2011 at 6:38 am

  #1413395

  This is more difficult than the previous version by a fair way. Before, I simply generated a "CASE" statement to find the string.

  With the new version, I'm looking at doing the same because it keeps IO down.

  SELECT ID, Sequence

  FROM (SELECT ID, REPLACE(string,'%','') AS Sequence,

  ROW_NUMBER() OVER(PARTITION BY ID ORDER BY LEN(string) DESC, string) AS rn

  FROM (SELECT CASE WHEN b.String LIKE a.strings THEN a.strings ELSE NULL END AS string,

  b.ID, ROW_NUMBER() OVER(PARTITION BY ID ORDER BY LEN(strings) DESC, strings) AS rn

  FROM (SELECT COALESCE('%' + b.strings+a.strings + '%','%' + a.strings + '%') AS strings

  FROM (SELECT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ',t1.N,t2.N-t1.N+1) AS strings, t1.N

  FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

  (9),(10),(11),(12),(13),(14),(15),

  (16),(17),(18),(19),(20),(21),(22),

  (23),(24),(25),(26)) t1(N)

  CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

  (9),(10),(11),(12),(13),(14),(15),

  (16),(17),(18),(19),(20),(21),(22),

  (23),(24),(25),(26)) t2(N)

  WHERE t1.N <= t2.N) a

  LEFT OUTER JOIN (SELECT REVERSE(SUBSTRING('ZYXWVUTSRQPONMLKJIHGFEDCBA',1,N)) AS strings, 1 AS ID

  FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),

  (9),(10),(11),(12),(13),(14),(15),

  (16),(17),(18),(19),(20),(21),(22),

  (23),(24),(25),(26)) t1(N)

  UNION ALL SELECT '', 1) b ON a.N = b.ID) a

  CROSS JOIN dbo.TC70 b) a ) a

  WHERE a.rn = 1

  ORDER BY a.ID

  The above only works if the string wrap-around is no more than 1 (e.g. it'll pick up 'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ' or any combination of, but it won't pick up 'ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ').

  Should be fun to play around with when I have time.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---