{"id":9189,"date":"2016-10-31T16:01:06","date_gmt":"2016-10-31T15:01:06","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/"},"modified":"2016-10-31T16:01:06","modified_gmt":"2016-10-31T15:01:06","slug":"sql-server-2016-new-useful-function-string_split","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/","title":{"rendered":"SQL Server 2016: New useful function STRING_SPLIT()"},"content":{"rendered":"<p>Now, in the latest version of SQL Server, you have one of the most expected function as well as for developers as for administrators, splitting a string natively in T-SQL:<\/p>\n<p style=\"text-align: center\"><strong>STRING_SPLIT(&lt;character expression&gt;,&lt;separator&gt;)<\/strong><\/p>\n<p>This function has 2 parameters:<\/p>\n<ul>\n<li>The character expression with a data type of nvarchar,varchar,nchar or char<\/li>\n<li>The separator with a data type of nvarchar(<strong>1<\/strong>), varchar(<strong>1<\/strong>), nchar(<strong>1<\/strong>) or char(<strong>1<\/strong>)<\/li>\n<\/ul>\n<p>The function return a table of one column with all splitting string<\/p>\n<p>A first test with a text of mine on the<a title=\"Stephane Haby CV\" href=\"https:\/\/www.dbi-services.com\/on-the-company-and-its-associates\/our-associates-and-certified-experts\/stephane-haby\/\" target=\"_blank\"> dbi-services website<\/a> and the \u2018.\u2019 as separator:<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-12421\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01.jpg\" alt=\"string_split01\" width=\"958\" height=\"548\" \/><\/a><\/p>\n<p>If I use more than one character for the separator like \u2018. \u2019, an error message appears:<br \/>\n<span style=\"color: red\">Msg 214, Level 16, State 11, Line 1<br \/>\nProcedure expects parameter &#8216;separator&#8217; of type &#8216;nchar(1)\/nvarchar(1)&#8217;.<\/span><br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split02.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-12423\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split02.jpg\" alt=\"string_split02\" width=\"742\" height=\"362\" \/><\/a><\/p>\n<p>This function is very useful if you have a list like the countries beginning with A:<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split03.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-12422\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split03.jpg\" alt=\"string_split03\" width=\"1044\" height=\"476\" \/><\/a><br \/>\nAnother usage is for a folder path:<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split04.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-12424\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split04.jpg\" alt=\"string_split04\" width=\"690\" height=\"377\" \/><\/a><br \/>\nThis is a nice, easy and useful function but limited to one character as separator for the moment\u2026<br \/>\nMsdn link to the function <a title=\"msdn string_split()\" href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/mt684588.aspx\" target=\"_blank\">here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Now, in the latest version of SQL Server, you have one of the most expected function as well as for developers as for administrators, splitting a string natively in T-SQL: STRING_SPLIT(&lt;character expression&gt;,&lt;separator&gt;) This function has 2 parameters: The character expression with a data type of nvarchar,varchar,nchar or char The separator with a data type of [&hellip;]<\/p>\n","protected":false},"author":15,"featured_media":9194,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[368],"tags":[49,566],"type_dbi":[],"class_list":["post-9189","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-development-performance","tag-microsoft","tag-sql-server-2016"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>SQL Server 2016: New useful function STRING_SPLIT() - dbi Blog<\/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:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Server 2016: New useful function STRING_SPLIT()\" \/>\n<meta property=\"og:description\" content=\"Now, in the latest version of SQL Server, you have one of the most expected function as well as for developers as for administrators, splitting a string natively in T-SQL: STRING_SPLIT(&lt;character expression&gt;,&lt;separator&gt;) This function has 2 parameters: The character expression with a data type of nvarchar,varchar,nchar or char The separator with a data type of [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-10-31T15:01:06+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"958\" \/>\n\t<meta property=\"og:image:height\" content=\"548\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"St\u00e9phane Haby\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"St\u00e9phane Haby\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/\"},\"author\":{\"name\":\"St\u00e9phane Haby\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"headline\":\"SQL Server 2016: New useful function STRING_SPLIT()\",\"datePublished\":\"2016-10-31T15:01:06+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/\"},\"wordCount\":177,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01-1.jpg\",\"keywords\":[\"Microsoft\",\"SQL Server 2016\"],\"articleSection\":[\"Development &amp; Performance\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/\",\"name\":\"SQL Server 2016: New useful function STRING_SPLIT() - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01-1.jpg\",\"datePublished\":\"2016-10-31T15:01:06+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01-1.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01-1.jpg\",\"width\":958,\"height\":548},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"SQL Server 2016: New useful function STRING_SPLIT()\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b\",\"name\":\"St\u00e9phane Haby\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g\",\"caption\":\"St\u00e9phane Haby\"},\"description\":\"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \/ Financial Services, and many other industries.\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"SQL Server 2016: New useful function STRING_SPLIT() - dbi Blog","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:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/","og_locale":"en_US","og_type":"article","og_title":"SQL Server 2016: New useful function STRING_SPLIT()","og_description":"Now, in the latest version of SQL Server, you have one of the most expected function as well as for developers as for administrators, splitting a string natively in T-SQL: STRING_SPLIT(&lt;character expression&gt;,&lt;separator&gt;) This function has 2 parameters: The character expression with a data type of nvarchar,varchar,nchar or char The separator with a data type of [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/","og_site_name":"dbi Blog","article_published_time":"2016-10-31T15:01:06+00:00","og_image":[{"width":958,"height":548,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01-1.jpg","type":"image\/jpeg"}],"author":"St\u00e9phane Haby","twitter_card":"summary_large_image","twitter_misc":{"Written by":"St\u00e9phane Haby","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/"},"author":{"name":"St\u00e9phane Haby","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"headline":"SQL Server 2016: New useful function STRING_SPLIT()","datePublished":"2016-10-31T15:01:06+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/"},"wordCount":177,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01-1.jpg","keywords":["Microsoft","SQL Server 2016"],"articleSection":["Development &amp; Performance"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/","url":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/","name":"SQL Server 2016: New useful function STRING_SPLIT() - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01-1.jpg","datePublished":"2016-10-31T15:01:06+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01-1.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/string_split01-1.jpg","width":958,"height":548},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/sql-server-2016-new-useful-function-string_split\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"SQL Server 2016: New useful function STRING_SPLIT()"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/d0bfb7484ae81c8980fc2b11334f803b","name":"St\u00e9phane Haby","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1123227ca39a5dca608c0f72d23cd1904fee29979749bbb3a485b9438436c553?s=96&d=mm&r=g","caption":"St\u00e9phane Haby"},"description":"St\u00e9phane Haby has more than ten years of experience in Microsoft solutions. He is specialized in SQL Server technologies such as installation, migration, best practices, and performance analysis etc. He is also an expert in Microsoft Business Intelligence solutions such as SharePoint, SQL Server and Office. Futhermore, he has many years of .NET development experience in the banking sector and other industries. In France, he was one of the first people to have worked with Microsoft Team System. He has written several technical articles on this subject. St\u00e9phane Haby is Microsoft Most Valuable Professional (MVP) as well as Microsoft Certified Solutions Associate (MCSA) and\u00a0Microsoft Certified Solutions Expert (MCSE) for SQL Server 2012. He is also Microsoft Certified Technology Specialist (MCTS) and Microsoft Certified IT Professional (MCITP) for SQL Server 2008 as well as ITIL Foundation V3 certified. He holds a Engineer diploma in industrial computing and automation from France. His branch-related experience covers Chemicals &amp; Pharmaceuticals, Banking \/ Financial Services, and many other industries.","url":"https:\/\/www.dbi-services.com\/blog\/author\/stephane-haby\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9189","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/15"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=9189"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/9189\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/9194"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=9189"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=9189"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=9189"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=9189"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}