InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 16:04:58 GMT20http://www.sqlservercentral.com/Forums/Topic1212208-356-1.aspxThis 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.[code="sql"]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 &lt;= 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 ) aWHERE a.rn = 1ORDER BY a.ID[/code]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.Mon, 28 Nov 2011 06:38:05 GMTCadavrehttp://www.sqlservercentral.com/Forums/Topic1212208-356-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/T-SQL+Challenges/76930/">TSQL Challenge 70 - Find the longest sequence of alphabets in a string</A>[/B]Sun, 27 Nov 2011 08:56:34 GMTjacob sebastian