Advertisement
EmilySamantha80

Find the longest alphabetical sequence in strings.sql

Jun 2nd, 2017
236
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 3.99 KB | None | 0 0
  1. SET NOCOUNT ON
  2.  
  3. ----
  4. ---- Prereq:
  5. ---- Table with a unique unique_id field and a search_string field to do the sequential search on
  6. ---- In this demo, the table is called Source
  7. ----
  8.  
  9. --Holds vital information for processing as well as the results
  10. DECLARE @details AS table
  11. (
  12.         unique_id nvarchar(100)
  13.     ,   search_string varchar(100)
  14.     ,   longestSequence varchar(100)
  15.     ,   processed bit
  16. )
  17.  
  18. --Fill up the table with all of the information
  19. INSERT @details
  20. (
  21.         unique_id
  22.     ,   search_string
  23.     ,   longestSequence
  24.     ,   processed
  25. )
  26. SELECT
  27.         unique_id
  28.     ,   search_string
  29.     ,   NULL
  30.     ,   0
  31. FROM Source
  32.  
  33. --Holds the unique_id of the current record being processed
  34. DECLARE @id nvarchar(100)
  35.  
  36. --Get the first unique_id that hasn't been processed
  37. SET @id = (SELECT TOP 1 unique_id FROM @details WHERE processed = 0 ORDER BY unique_id)
  38. --Loop through the ID's until there are no processed items left
  39. WHILE @id IS NOT NULL
  40. BEGIN
  41.     --Hold the current search string
  42.     DECLARE @ss varchar(100)
  43.     SET @ss = (SELECT TOP 1 search_string FROM @details WHERE unique_id = @id)
  44.  
  45.     --Hold the current position that is being evaluated from in the search string
  46.     DECLARE @pos int
  47.     SET @pos = 1
  48.  
  49.     --Hold the various sequential strings in the substring. It is cleared out before @pos is incremented
  50.     DECLARE @ssSequence AS table
  51.     (
  52.             character_id int
  53.         ,   sequence varchar(100)
  54.     )
  55.  
  56.     --Loop through each character in the search string
  57.     WHILE @pos < LEN(@ss)
  58.     BEGIN
  59.         --Hold the current alphabetical sequence
  60.         DECLARE @sequence varchar(100)
  61.  
  62.         --Hold the position of the substring search that starts at @pos and goes to the end of the search string
  63.         DECLARE @subPos int
  64.         SET @subPos = @pos
  65.  
  66.         --Hold the current character that's being evaluated
  67.         DECLARE @c char
  68.         SET @c = SUBSTRING(@ss, @pos, 1)
  69.        
  70.         --Start out the current sequence with the character that's being evaluated
  71.         SET @sequence = @c
  72.  
  73.         --Evaluate each character in the substring up to the point where it's no longer sequential
  74.         WHILE @subPos < LEN(@ss)
  75.         BEGIN
  76.             --Hold the next character in the substring
  77.             DECLARE @c2 char
  78.  
  79.             --Store the current character and the next character being processed
  80.             SET @c = SUBSTRING(@ss, @subPos, 1)
  81.             SET @c2 = SUBSTRING(@ss, @subPos + 1, 1)
  82.  
  83.             --Check if the next character in the substring is sequential
  84.             IF ASCII(@c2) = ASCII(@c) + 1
  85.             BEGIN
  86.                 --If it is sequential, append the character to the sequence string
  87.                 SET @sequence = @sequence + @c2
  88.             END
  89.             ELSE
  90.             BEGIN
  91.                 --It's not sequential, no need to evaluate further
  92.                 BREAK
  93.             END
  94.            
  95.             --Increment the substring position
  96.             SET @subPos = @subPos + 1
  97.         END
  98.  
  99.         --The substring search is complete, insert the sequential string created in the last loop, if there is one
  100.         IF LEN(@sequence) > 1
  101.         BEGIN
  102.             INSERT @ssSequence
  103.             (
  104.                     character_id
  105.                 ,   sequence
  106.             ) VALUES
  107.             (
  108.                     @pos
  109.                 ,   @sequence
  110.             )
  111.         END
  112.  
  113.         --Go to the next starting position in the search string and evaluate from there
  114.         SET @pos = @pos + 1
  115.     END
  116.    
  117.     --Holds the longest sequential string for the current search string
  118.     DECLARE @longestSequence varchar(50)
  119.     SET @longestSequence = (SELECT TOP 1 sequence FROM @ssSequence WHERE ISNUMERIC(sequence) = 0 ORDER BY LEN(sequence) DESC)
  120.  
  121.     --Update the table with the longest string and the fact that it's been processed
  122.     UPDATE @details SET longestSequence = @longestSequence, processed = 1 WHERE unique_id = @id
  123.  
  124.     --Clear the table that holds the search string sequences for the substring before going on to the next position in the search string
  125.     DELETE @ssSequence
  126.  
  127.     --Get the next user_id that hasn't been processed
  128.     SET @id = (SELECT TOP 1 unique_id FROM @details WHERE processed = 0 ORDER BY unique_id)
  129. END
  130.  
  131. SELECT
  132.         Source.unique_id
  133.     ,   Source.search_string
  134.     ,   ud.longestSequence
  135.     ,   ISNULL(LEN(ud.longestSequence),0) AS length
  136. FROM    Source
  137. INNER JOIN @details AS ud ON (Source.unique_id = ud.unique_id)
  138. ORDER BY
  139.         LEN(ud.longestSequence) DESC
  140.     ,   ud.longestSequence DESC
  141.     ,   Source.unique_id ASC
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement