I made this query to answer a challenge given from a programming forum. After the query is done I realized that I\u2019ve made a mistake. And it was quite a mistake. The challenge ask you to list all the character that occur in sequence. What I made is a query to list the most sequential occurrence character..<\/p>\n
I know this query is far from perfect. But just let me share it with you.<\/p>\n
this is the data source<\/p>\n
\nData\n------------------------------------\n9992EDC6-D117-4DEE-B410-4E5FAE46AE97\n0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1\n4A73E7EB-7777-4A04-9258-F1E75097977C\n5AAF477C-274D-400D-9067-035968F33B19\n725DA718-30D0-44A9-B36A-89F27CDFEEDE\n8083ED5A-D3B9-4694-BB04-F0B09C588888\n22244444-43B9-4694-BB04-F0B098888888<\/pre>\n<\/blockquote>\nexpected result :<\/p>\n
\nData\t\t\t\t pattern\tlen\tpattern2\tlen2\n----------------------------------------------------------------------------\n9992EDC6-D117-4DEE-B410-4E5FAE46AE97\t999\t3\tNULL\t\tNULL\n0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1\tNULL\tNULL\tNULL\t\tNULL\n4A73E7EB-7777-4A04-9258-F1E75097977C\t7777\t4\tNULL\t\tNULL\n5AAF477C-274D-400D-9067-035968F33B19\tAA\t2\tNULL\t\tNULL\n725DA718-30D0-44A9-B36A-89F27CDFEEDE\t44\t2\tNULL\t\tNULL\n8083ED5A-D3B9-4694-BB04-F0B09C588888\t88888\t5\tNULL\t\tNULL\n22244444-43B9-4694-BB04-F0B098888888\t8888888\t7\tNULL\t\tNULL\n\nDATA\t\t\t\t\tChar\tPos\tLen\n------------------------------------------------------------\n9992EDC6-D117-4DEE-B410-4E5FAE46AE97\t9\t1\t3\n0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1\tNULL\tNULL\tNULL\n4A73E7EB-7777-4A04-9258-F1E75097977C\t7\t10\t4\n5AAF477C-274D-400D-9067-035968F33B19\tA\t2\t2\n725DA718-30D0-44A9-B36A-89F27CDFEEDE\t4\t15\t2\n8083ED5A-D3B9-4694-BB04-F0B09C588888\t8\t32\t5\n22244444-43B9-4694-BB04-F0B098888888\t8\t30\t7<\/pre>\n<\/blockquote>\nMy query<\/p>\n
\nCreate table #t (Data VARCHAR(40), pattern varchar(50), [len] int,\npattern2 varchar(50), len2 int )\n\nINSERT #t (Data) SELECT '9992EDC6-D117-4DEE-B410-4E5FAE46AE97'\nINSERT #t (Data) SELECT '0BFC936B-BD9A-4C6A-AFB2-CF3F1752F8B1'\nINSERT #t (Data) SELECT '4A73E7EB-7777-4A04-9258-F1E75097977C'\nINSERT #t (Data) SELECT '5AAF477C-274D-400D-9067-035968F33B19'\nINSERT #t (Data) SELECT '725DA718-30D0-44A9-B36A-89F27CDFEEDE'\nINSERT #t (Data) SELECT '8083ED5A-D3B9-4694-BB04-F0B09C588888'\nINSERT #t (Data) SELECT '22244444-43B9-4694-BB04-F0B098888888'\n\nselect * from #t\n\ndeclare @pattern varchar(20)\nset @pattern = null\ndeclare @maxlen int\ndeclare @e int\ndeclare @i varchar(2)\ndeclare @j varchar(2)\n\nset @maxlen = (select MAX(len(data)) from #t)\nprint @maxlen\n\ndeclare @q nvarchar(4000)\n\nset @i = 1\nset @j = 2\n\nwhile (@i < 36)\n begin\n print @i\n\n set @q = '\n update\u00a0 #t\n set pattern = right(isnull(pattern,''''),isnull(len(pattern),1)-1) +\n SUBSTRING(data, '+@i+', 1) + SUBSTRING(Data,'+@j+',1)\n where SUBSTRING(data, '+@i+', 1) = SUBSTRING(Data,'+@j+',1)\n and RIGHT(ISNULL(pattern, SUBSTRING(Data,'+@i+',1)),1) = SUBSTRING(Data,'+@i+',1)\n and isnull(substring(data, '+@i+'-[len]+1, 1),SUBSTRING(data, '+@i+', 1))\n =\u00a0 SUBSTRING(data, '+@i+', 1)\n\n update\u00a0 #t\n set pattern2 = right(isnull(pattern2,''''),isnull(len(pattern2),1)-1) +\n SUBSTRING(data, '+@i+', 1) + SUBSTRING(Data,'+@j+',1)\n where SUBSTRING(data, '+@i+', 1) = SUBSTRING(Data,'+@j+',1)\n and RIGHT(ISNULL(pattern2, SUBSTRING(Data,'+@i+',1)),1)\n = SUBSTRING(Data,'+@i+',1)\n and isnull(substring(data, '+@j+'-[len2]+1, 1),\n SUBSTRING(data, '+@i+', 1)) =\u00a0 SUBSTRING(data, '+@i+', 1)\n and [len] != 0\n\n update #t\n set [len] = len(pattern),\n len2 = len(pattern2)\n\n update #t\n set [pattern] = [pattern2],\n [len]= len2,\n pattern2 = NULL\n where len2 > [len]\n and SUBSTRING(data, '+@j+', 1) <> SUBSTRING(data, '+@j+'+1, 1)\n\n update #t\n set [pattern2] = NULL,\n [len2]= NULL\n where SUBSTRING(data, '+@j+', 1) <> SUBSTRING(data, '+@j+'+1, 1)\n '\n\n print @q\n exec SP_executesql @q\n set @i +=1\n set @j +=1\n\n end\n\nselect * from #t\nselect DATA, LEFT(pattern,1) AS 'Char',\u00a0 PATINDEX('%'+pattern+'%', DATA) AS Pos, [Len]\n from #t<\/pre>\n<\/blockquote>\nBasically what I am trying to do is compare the char with the following char. And Keep the pattern and if I found another sequential occurrence the most frequent will be recorded.<\/p>\n","protected":false},"excerpt":{"rendered":"
I made this query to answer a challenge given from a programming forum. After the query is done I realized that I\u2019ve made a mistake. And it was quite a mistake. The challenge ask you to list all the character that occur in sequence. What I made is a query to list the most sequential […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[112],"tags":[23,38,54],"_links":{"self":[{"href":"http:\/\/www.raytawil.com\/index.php?rest_route=\/wp\/v2\/posts\/82"}],"collection":[{"href":"http:\/\/www.raytawil.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.raytawil.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.raytawil.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.raytawil.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=82"}],"version-history":[{"count":1,"href":"http:\/\/www.raytawil.com\/index.php?rest_route=\/wp\/v2\/posts\/82\/revisions"}],"predecessor-version":[{"id":166,"href":"http:\/\/www.raytawil.com\/index.php?rest_route=\/wp\/v2\/posts\/82\/revisions\/166"}],"wp:attachment":[{"href":"http:\/\/www.raytawil.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=82"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.raytawil.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=82"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.raytawil.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=82"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}