Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 TSQL Challenge 70 - Find the longest sequence of alphabets in a string Rate Topic Display Mode Topic Options
Author
 Message
 Posted Sunday, November 27, 2011 8:56 AM
 SSC-Addicted Group: General Forum Members Last Login: Tuesday, December 24, 2013 4:42 AM Points: 460, Visits: 2,523
 Comments posted to this topic are about the item TSQL Challenge 70 - Find the longest sequence of alphabets in a string .
Post #1212208
 Posted Monday, November 28, 2011 6:38 AM
 SSCrazy Group: General Forum Members Last Login: Tuesday, November 29, 2016 4:40 AM Points: 2,492, Visits: 8,429
 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 ) aWHERE a.rn = 1ORDER 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 learnFor 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/If you litter your database queries with nolock query hints, are you aware of the side effects?Try reading a few of these links...(*) Missing rows with nolock(*) Allocation order scans with nolock(*) Consistency issues with nolock(*) Transient Corruption Errors in SQL Server error log caused by nolock(*) Dirty reads, read errors, reading rows twice and missing rows with nolockCraig Wilkinson - Software EngineerLinkedIn
Post #1212461

 Permissions