{"id":1270,"date":"2020-11-17T10:00:07","date_gmt":"2020-11-17T15:00:07","guid":{"rendered":"https:\/\/novicksoftware.com\/?p=1270"},"modified":"2020-11-18T09:02:06","modified_gmt":"2020-11-18T14:02:06","slug":"improve-sql-string-parsing","status":"publish","type":"post","link":"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/","title":{"rendered":"Improve SQL String Parsing"},"content":{"rendered":"

The number of ways to receive information seems to increase all the time. One of the ways to get information from the web is an RSS feed. If you use a feed reader.<\/p>\n

This issue features a contribution from reader Nick Barclay from Australia. The function, InString, is one he’s found useful for parsing strings into their parts. The function returns the character position of the character after the Nth instance of the search string. It’s useful for parsing delimited strings. Here’s the CREATE FUNCTION script:<\/p>\n

SET QUOTED_IDENTIFIER ON
\nSET ANSI_NULLS ON
\nSET NOCOUNT ON
\nGOCREATE function InString(@string varchar(200),
\n@searchfor varchar(50),
\n@position int
\n) returns int<\/p>\n

\/*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
\nReturns the position of the character AFTER the nth instance
\nof the string<\/p>\n

— TEST CASE #1
\n— should return 18
\nselect dbo.InString(‘123456 123456 123456 123456′, ’23’, 3)
\nas [Test Case #1]<\/p>\n

— TEST CASE #2
\n— should return 25
\nselect dbo.InString(‘test1\/test2\/test3\/test4\/test5\/’, ‘\/’, 4)
\nas [Test Case #2]<\/p>\n

— TEST CASE #3
\ndeclare @teststring varchar(50)
\nset @teststring = ‘test1\/test2\/test3\/test4\/test5\/’
\nselect substring(@teststring, dbo.Instring(@teststring,’\/’,3),5)
\nas [Test Case #3]
\n— should return ‘test4’<\/p>\n

— TEST CASE #4 (variable length delimited fields
\ndeclare @teststring2 varchar(50)
\nset @teststring2 = ‘test123\/test\/testtestestest\/testxyz\/test\/’
\nselect substring( @teststring2,
\ndbo.Instring(@teststring2, ‘\/’, 3),
\n(dbo.Instring(@teststring2, ‘\/’, 4) -1)
\n– dbo.Instring(@teststring2, ‘\/’, 3)
\n) as [Test Case #4]
\n— should return ‘testxyz’
\n+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*\/<\/p>\n

as
\nbegin
\ndeclare @lenstring int, @poscount int, @stringpos int
\nset @lenstring = datalength(@searchfor)
\nset @poscount = 1
\nset @stringpos = 1
\nwhile @poscount <= @position and @stringpos <= len(@string)
\nbegin
\n— if we find the string segment we’re looking for
\nif substring(@string, @stringpos, @lenstring)=@searchfor
\nbegin
\n— is the instance of the string the one we are
\n— looking for?
\nif @poscount = @position
\nbegin
\nset @stringpos = @stringpos + @lenstring
\nreturn @stringpos
\nend
\n— else look for the next instance of the string
\n— segment
\nelse
\nbegin
\nset @poscount = @poscount + 1
\nend
\nend
\nset @stringpos = @stringpos + 1
\nend
\nreturn null
\nend
\nGO<\/p>\n

GRANT EXEC on dbo.InString TO PUBLIC
\nGO<\/p>\n<\/div>\n

The tests from the function header illustrate how the function works so lets use them as the demonstration:<\/p>\n

— TEST CASE #1
\n— should return 18
\nselect dbo.InString(‘123456 123456 123456 123456′, ’23’, 3)
\nas [Test Case #1]– TEST CASE #2
\n— should return 25
\nselect dbo.InString(‘test1\/test2\/test3\/test4\/test5\/’, ‘\/’, 4)
\nas [Test Case #2]– TEST CASE #3
\ndeclare @teststring varchar(50)
\nset @teststring = ‘test1\/test2\/test3\/test4\/test5\/’
\nselect substring(@teststring, dbo.Instring(@teststring,’\/’,3),5)
\nas [Test Case #3]
\n— should return ‘test4’<\/p>\n

— TEST CASE #4 (variable length delimited fields
\ndeclare @teststring2 varchar(50)
\nset @teststring2 = ‘test123\/test\/testtestestest\/testxyz\/test\/’
\nselect substring( @teststring2,
\ndbo.Instring(@teststring2, ‘\/’, 3),
\n(dbo.Instring(@teststring2, ‘\/’, 4) -1)
\n– dbo.Instring(@teststring2, ‘\/’, 3)
\n) as [Test Case #4]
\n— should return ‘testxyz’
\nGO<\/p>\n<\/div>\n

Results:<\/p>\n\n\n\n\n
Test Case #1<\/th>\n<\/tr>\n
18<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n
Test Case #2<\/th>\n<\/tr>\n
25<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n
Test Case #3<\/th>\n<\/tr>\n
test4<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n\n\n\n\n
Test Case #4<\/th>\n<\/tr>\n
testxyz<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Test cases 3 and 4 really illustrate how to use the function in a real situation. Take a closer look at test 3:<\/p>\n

declare @teststring varchar(50) set @teststring = ‘test1\/test2\/test3\/test4\/test5\/’ select substring(@teststring, dbo.Instring(@teststring,’\/’,3),5) as [Test Case #3]<\/div>\n

In this instance, we have strings that are five characters separated by backslash (\/). The expression returns the string after the 3rd backslash.<\/p>\n

Thanks to Nick for his contribution.<\/p>\n","protected":false},"excerpt":{"rendered":"

The number of ways to receive information seems to increase all the time. One of the ways to get information from the web is an RSS feed. If you use a feed reader. This issue features a contribution from reader Nick Barclay from Australia. The function, InString, is one he’s found useful for parsing strings […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[6],"tags":[],"class_list":["post-1270","post","type-post","status-publish","format-standard","hentry","category-sql-coding"],"jetpack_publicize_connections":[],"yoast_head":"\nImprove SQL String Parsing - Novick Software<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Improve SQL String Parsing - Novick Software\" \/>\n<meta property=\"og:description\" content=\"The number of ways to receive information seems to increase all the time. One of the ways to get information from the web is an RSS feed. If you use a feed reader. This issue features a contribution from reader Nick Barclay from Australia. The function, InString, is one he’s found useful for parsing strings […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/\" \/>\n<meta property=\"og:site_name\" content=\"Novick Software\" \/>\n<meta property=\"article:published_time\" content=\"2020-11-17T15:00:07+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-11-18T14:02:06+00:00\" \/>\n<meta name=\"author\" content=\"Ian Bachman-Sanders\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ian Bachman-Sanders\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/\"},\"author\":{\"name\":\"Ian Bachman-Sanders\",\"@id\":\"https:\/\/novicksoftware.com\/#\/schema\/person\/d8ba84bd499de646b3ee46a5b0e20224\"},\"headline\":\"Improve SQL String Parsing\",\"datePublished\":\"2020-11-17T15:00:07+00:00\",\"dateModified\":\"2020-11-18T14:02:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/\"},\"wordCount\":551,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/novicksoftware.com\/#organization\"},\"articleSection\":[\"SQL coding\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/\",\"url\":\"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/\",\"name\":\"Improve SQL String Parsing - Novick Software\",\"isPartOf\":{\"@id\":\"https:\/\/novicksoftware.com\/#website\"},\"datePublished\":\"2020-11-17T15:00:07+00:00\",\"dateModified\":\"2020-11-18T14:02:06+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/novicksoftware.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Improve SQL String Parsing\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/novicksoftware.com\/#website\",\"url\":\"https:\/\/novicksoftware.com\/\",\"name\":\"Novick Software\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/novicksoftware.com\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/novicksoftware.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/novicksoftware.com\/#organization\",\"name\":\"Novick Software\",\"url\":\"https:\/\/novicksoftware.com\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/novicksoftware.com\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/secureservercdn.net\/198.71.233.185\/o7v.274.myftpupload.com\/wp-content\/uploads\/2014\/07\/novick_header_wide1.jpg?time=1618718701\",\"contentUrl\":\"https:\/\/secureservercdn.net\/198.71.233.185\/o7v.274.myftpupload.com\/wp-content\/uploads\/2014\/07\/novick_header_wide1.jpg?time=1618718701\",\"width\":1140,\"height\":100,\"caption\":\"Novick Software\"},\"image\":{\"@id\":\"https:\/\/novicksoftware.com\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/novicksoftware.com\/#\/schema\/person\/d8ba84bd499de646b3ee46a5b0e20224\",\"name\":\"Ian Bachman-Sanders\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/novicksoftware.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/c7440c74041bd8c4961cf8a2b3a1f962e7808b48c7fffe6b758314cce008efe4?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/c7440c74041bd8c4961cf8a2b3a1f962e7808b48c7fffe6b758314cce008efe4?s=96&d=mm&r=g\",\"caption\":\"Ian Bachman-Sanders\"},\"url\":\"https:\/\/novicksoftware.com\/author\/ns-ian-bs\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Improve SQL String Parsing - Novick Software","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/","og_locale":"en_US","og_type":"article","og_title":"Improve SQL String Parsing - Novick Software","og_description":"The number of ways to receive information seems to increase all the time. One of the ways to get information from the web is an RSS feed. If you use a feed reader. This issue features a contribution from reader Nick Barclay from Australia. The function, InString, is one he’s found useful for parsing strings […]","og_url":"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/","og_site_name":"Novick Software","article_published_time":"2020-11-17T15:00:07+00:00","article_modified_time":"2020-11-18T14:02:06+00:00","author":"Ian Bachman-Sanders","twitter_misc":{"Written by":"Ian Bachman-Sanders","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/#article","isPartOf":{"@id":"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/"},"author":{"name":"Ian Bachman-Sanders","@id":"https:\/\/novicksoftware.com\/#\/schema\/person\/d8ba84bd499de646b3ee46a5b0e20224"},"headline":"Improve SQL String Parsing","datePublished":"2020-11-17T15:00:07+00:00","dateModified":"2020-11-18T14:02:06+00:00","mainEntityOfPage":{"@id":"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/"},"wordCount":551,"commentCount":0,"publisher":{"@id":"https:\/\/novicksoftware.com\/#organization"},"articleSection":["SQL coding"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/novicksoftware.com\/improve-sql-string-parsing\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/","url":"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/","name":"Improve SQL String Parsing - Novick Software","isPartOf":{"@id":"https:\/\/novicksoftware.com\/#website"},"datePublished":"2020-11-17T15:00:07+00:00","dateModified":"2020-11-18T14:02:06+00:00","breadcrumb":{"@id":"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/novicksoftware.com\/improve-sql-string-parsing\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/novicksoftware.com\/improve-sql-string-parsing\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/novicksoftware.com\/"},{"@type":"ListItem","position":2,"name":"Improve SQL String Parsing"}]},{"@type":"WebSite","@id":"https:\/\/novicksoftware.com\/#website","url":"https:\/\/novicksoftware.com\/","name":"Novick Software","description":"","publisher":{"@id":"https:\/\/novicksoftware.com\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/novicksoftware.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/novicksoftware.com\/#organization","name":"Novick Software","url":"https:\/\/novicksoftware.com\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/novicksoftware.com\/#\/schema\/logo\/image\/","url":"https:\/\/secureservercdn.net\/198.71.233.185\/o7v.274.myftpupload.com\/wp-content\/uploads\/2014\/07\/novick_header_wide1.jpg?time=1618718701","contentUrl":"https:\/\/secureservercdn.net\/198.71.233.185\/o7v.274.myftpupload.com\/wp-content\/uploads\/2014\/07\/novick_header_wide1.jpg?time=1618718701","width":1140,"height":100,"caption":"Novick Software"},"image":{"@id":"https:\/\/novicksoftware.com\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/novicksoftware.com\/#\/schema\/person\/d8ba84bd499de646b3ee46a5b0e20224","name":"Ian Bachman-Sanders","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/novicksoftware.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/c7440c74041bd8c4961cf8a2b3a1f962e7808b48c7fffe6b758314cce008efe4?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/c7440c74041bd8c4961cf8a2b3a1f962e7808b48c7fffe6b758314cce008efe4?s=96&d=mm&r=g","caption":"Ian Bachman-Sanders"},"url":"https:\/\/novicksoftware.com\/author\/ns-ian-bs\/"}]}},"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p4VNnB-ku","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/novicksoftware.com\/wp-json\/wp\/v2\/posts\/1270","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/novicksoftware.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/novicksoftware.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/novicksoftware.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/novicksoftware.com\/wp-json\/wp\/v2\/comments?post=1270"}],"version-history":[{"count":4,"href":"https:\/\/novicksoftware.com\/wp-json\/wp\/v2\/posts\/1270\/revisions"}],"predecessor-version":[{"id":2164,"href":"https:\/\/novicksoftware.com\/wp-json\/wp\/v2\/posts\/1270\/revisions\/2164"}],"wp:attachment":[{"href":"https:\/\/novicksoftware.com\/wp-json\/wp\/v2\/media?parent=1270"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/novicksoftware.com\/wp-json\/wp\/v2\/categories?post=1270"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/novicksoftware.com\/wp-json\/wp\/v2\/tags?post=1270"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}