{"id":7019,"date":"2024-02-29T07:02:00","date_gmt":"2024-02-29T06:02:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=7019"},"modified":"2024-03-01T16:57:58","modified_gmt":"2024-03-01T15:57:58","slug":"how-to-split-delimited-string-into-one-column-in-sql-server","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/","title":{"rendered":"How to Split Delimited String into One Column in SQL Server ?"},"content":{"rendered":"\n<h4 class=\"wp-block-heading has-text-align-left\"><em>How to split data from a unique string in line with a delimiter into one column using SQL Server ?<\/em><\/h4>\n\n\n\n<p>Tutorial on how to split a delimited string into one unique column in SQL Server, the delimiter used is usually a semicolon or a comma. With a T-SQL query in SQL Server 2012, it is possible to cut a string compounded of text based on a special character with XML. It can be delimited by commas, semicolons, tabulations, dash, underscore, or even dots. It can basically be any special character that defines the delimited text. This tutorial is for SQL Server versions prior to 2016, i.e. SQL Server 2008 and SQL Server 2012 for example.<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 ez-toc-wrap-center counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#1-about-delimited-strings-in-sql-server-2008-and-2012\" >1. About delimited strings in SQL Server 2008 and 2012<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#2-t-sql-query-to-split-string-by-delimiter-into-a-column\" >2. T-SQL query to split string by delimiter into a column<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#3-intermediate-result-of-the-split-string-in-xml-format\" >3. Intermediate result of the split string in XML format<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"h-1-about-delimited-strings-in-sql-server-2008-and-2012\"><span class=\"ez-toc-section\" id=\"1-about-delimited-strings-in-sql-server-2008-and-2012\"><\/span>1. About delimited strings in SQL Server 2008 and 2012<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Indeed, for example, the easy and efficient way is to use SQL Server XML built-in functions like CONVERT() and NODES(). Then use a Transact-SQL query to split the text into multiple lines. Indeed, the power of XML functions allows the SQL developer to parse and structure text more easily. For example, let&#8217;s cut easily this string containing the ten biggest US cities delimited with semicolons:<\/p>\n\n\n\n<p><strong>&#8220;New York; Los Angeles; Chicago; Houston; Phoenix; Philadelphia; San Antonio; San Diego; Dallas; San Jose&#8221;<\/strong><\/p>\n\n\n\n<p>This example works with any type of separators also called delimiters. The most used ones are:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Comma-delimited file<\/strong><\/li>\n\n\n\n<li><strong>Semi-column delimited file<\/strong><\/li>\n\n\n\n<li><strong>Tab-delimited file<\/strong><\/li>\n\n\n\n<li><strong>Vertical bar delimited file<\/strong><\/li>\n\n\n\n<li><strong>Dash delimited file<\/strong><\/li>\n<\/ul>\n\n\n\n<p>And this XML solution allows you to <strong>split texts on versions prior to SQL Server 2016<\/strong>, such as : SQL Server 2008 R2, SQL Server 2012 and SQL Server 2014.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-2-t-sql-query-to-split-string-by-delimiter-into-a-column\"><span class=\"ez-toc-section\" id=\"2-t-sql-query-to-split-string-by-delimiter-into-a-column\"><\/span>2. T-SQL query to split string by delimiter into a column<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>For instance, this Transact-SQL query splits the string containing the list of cities delimited by semicolons using the XML built-in functions.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">-- Declare the variables\nDECLARE @String nvarchar(max),\n@Delimiter char(1),\n@XMLString xml;\n\n-- Initialize the string and the delimiter, here it's the semicolon character \nSELECT @String = 'New York;Los Angeles;Chicago;Houston;Phoenix;Philadelphia;San Antonio;San Diego;Dallas;San Jose',\n@Delimiter = ';'\n\n-- XML String construction using T-SQL CONVERT() function\nSET @XMLString = \nCONVERT(xml,'&lt;root>&lt;city>' +\nREPLACE(@String,@Delimiter,'&lt;\/city>&lt;city>') +\n'&lt;\/city>&lt;\/root>');\n\nSELECT @XMLString;\n\n-- Select of the result by parsing the @XMLString variable with the .value() .nodes() XML functions\nSELECT Result.value('.','varchar(20)') AS CITY\nFROM @XMLString.nodes('\/root\/city') AS T(Result);\n<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-3-intermediate-result-of-the-split-string-in-xml-format\"><span class=\"ez-toc-section\" id=\"3-intermediate-result-of-the-split-string-in-xml-format\"><\/span>3. Intermediate result of the split string in XML format<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Indeed, to understand the logic of the query, this delimited string was initially contained in the @String variable, as simple text. And the @XMLString variable only contains the initial list of cities in XML format. However, this time the <strong>list is surrounded by XML tags &lt;root>&lt;\/root> and &lt;city>&lt;\/city><\/strong>. XML is a famous complex data format commonly used in data <a href=\"https:\/\/ecosio.com\/en\/blog\/edi-file-formats-explained\/\" target=\"_blank\" rel=\"noreferrer noopener\">exchange<\/a> files.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"xml\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">&lt;root>\n\t&lt;city>New York&lt;\/city>\n\t&lt;city>Los Angeles&lt;\/city>\n\t&lt;city>Chicago&lt;\/city>\n\t&lt;city>Houston&lt;\/city>\n\t&lt;city>Phoenix&lt;\/city>\n\t&lt;city>Philadelphia&lt;\/city>\n\t&lt;city>San Antonio&lt;\/city>\n\t&lt;city>San Diego&lt;\/city>\n\t&lt;city>Dallas&lt;\/city>\n\t&lt;city>San Jose&lt;\/city>\n&lt;\/root><\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"780\" height=\"760\" src=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/01\/t-sql_split_string_delimiter_into_one_column.jpg\" alt=\"Split string with delimiter into one column with SQL Server\" class=\"wp-image-21982\" srcset=\"https:\/\/expert-only.com\/wp-content\/uploads\/2023\/01\/t-sql_split_string_delimiter_into_one_column.jpg 780w, https:\/\/expert-only.com\/wp-content\/uploads\/2023\/01\/t-sql_split_string_delimiter_into_one_column-300x292.jpg 300w, https:\/\/expert-only.com\/wp-content\/uploads\/2023\/01\/t-sql_split_string_delimiter_into_one_column-768x748.jpg 768w\" sizes=\"auto, (max-width: 780px) 100vw, 780px\" \/><figcaption class=\"wp-element-caption\">Split string with delimiter into one column with SQL Server<\/figcaption><\/figure><\/div>\n\n\n<h3 class=\"wp-block-heading\">Different ways to split a delimited text<\/h3>\n\n\n\n<p>To summarize, this article shows how to split a delimited string into one unique column in SQL Server. The first example uses the XML functions and it&#8217;s easy to copy and paste. Hence reuse it for a real project scenario or proof of concept. Similarly for SQL Server 2016 and higher versions, you can simply use the Split_String native function.<\/p>\n\n\n\n<div class=\"schema-faq wp-block-yoast-faq-block\"><div class=\"schema-faq-section\" id=\"faq-question-1598550251408\"><strong class=\"schema-faq-question\">What is a delimited text? <\/strong> <p class=\"schema-faq-answer\">A delimited text is one unique line that has multiple parts. Each part is delimited with a specific symbol, for example, a semi-column, a tabulation, a vertical bar.<\/p> <\/div> <div class=\"schema-faq-section\" id=\"faq-question-1598550329146\"><strong class=\"schema-faq-question\">What is a text file with delimiters? <\/strong> <p class=\"schema-faq-answer\">A delimited text file is a file that has multiple columns and every data contained on each line is delimited by a separator. For instance, the line separator itself is a return carriage or a line break.<\/p> <\/div> <\/div>\n\n\n\n<p>Similarly, you might be interested to learn <a href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-manage-line-breaks-in-sql-server\/\"><strong>how to add or remove line break from SQL Server strings<\/strong><\/a> in order to store it in a single line of a table. And at the same time avoid line offsets when importing files.<\/p>\n\n\n\n<figure class=\"wp-block-embed aligncenter is-type-wp-embed is-provider-sql-and-it-tutorials wp-block-embed-sql-and-it-tutorials\"><div class=\"wp-block-embed__wrapper\">\n<blockquote class=\"wp-embedded-content\" data-secret=\"yXdGmMI8eg\"><a href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-manage-line-breaks-in-sql-server\/\">How To Manage Line Breaks in SQL Server ?<\/a><\/blockquote><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;How To Manage Line Breaks in SQL Server ?&#8221; &#8212; SQL and IT Tutorials\" src=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-manage-line-breaks-in-sql-server\/embed\/#?secret=nfnafvTRqy#?secret=yXdGmMI8eg\" data-secret=\"yXdGmMI8eg\" width=\"500\" height=\"282\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe>\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>How to split data from a unique string in line with a delimiter into one column using SQL Server ? Tutorial on how to split a delimited string into one unique column in SQL Server, the delimiter used is <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/\" title=\"How to Split Delimited String into One Column in SQL Server ?\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":5789,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[454],"tags":[],"class_list":{"0":"post-7019","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-t-sql"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How to Split Delimited String into One Column in SQL Server ?<\/title>\n<meta name=\"description\" content=\"To split delimited string with in SQL Server 2008, 2012 or 2014, use the XML functions in T-SQL queries to cut the text into one column.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to Split Delimited String into One Column in SQL Server ?\" \/>\n<meta property=\"og:description\" content=\"To split delimited string with in SQL Server 2008, 2012 or 2014, use the XML functions in T-SQL queries to cut the text into one column.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-02-29T06:02:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-01T15:57:58+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/pattern_dark_texture-design-9D617D2FB8C_1920x1080.jpeg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\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:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"How to Split Delimited String into One Column in SQL Server ?\",\"datePublished\":\"2024-02-29T06:02:00+00:00\",\"dateModified\":\"2024-03-01T15:57:58+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/\"},\"wordCount\":574,\"commentCount\":3,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/pattern_dark_texture-design-9D617D2FB8C_1920x1080.jpeg\",\"articleSection\":[\"T-SQL\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#respond\"]}]},{\"@type\":[\"WebPage\",\"FAQPage\"],\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/\",\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/\",\"name\":\"How to Split Delimited String into One Column in SQL Server ?\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/pattern_dark_texture-design-9D617D2FB8C_1920x1080.jpeg\",\"datePublished\":\"2024-02-29T06:02:00+00:00\",\"dateModified\":\"2024-03-01T15:57:58+00:00\",\"description\":\"To split delimited string with in SQL Server 2008, 2012 or 2014, use the XML functions in T-SQL queries to cut the text into one column.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#breadcrumb\"},\"mainEntity\":[{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550251408\"},{\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550329146\"}],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/pattern_dark_texture-design-9D617D2FB8C_1920x1080.jpeg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/pattern_dark_texture-design-9D617D2FB8C_1920x1080.jpeg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to Split Delimited String into One Column in SQL Server ?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}},{\"@type\":\"Question\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550251408\",\"position\":1,\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550251408\",\"name\":\"What is a delimited text?\",\"answerCount\":1,\"acceptedAnswer\":{\"@type\":\"Answer\",\"text\":\"A delimited text is one unique line that has multiple parts. Each part is delimited with a specific symbol, for example, a semi-column, a tabulation, a vertical bar.\",\"inLanguage\":\"en-US\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Question\",\"@id\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550329146\",\"position\":2,\"url\":\"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550329146\",\"name\":\"What is a text file with delimiters?\",\"answerCount\":1,\"acceptedAnswer\":{\"@type\":\"Answer\",\"text\":\"A delimited text file is a file that has multiple columns and every data contained on each line is delimited by a separator. For instance, the line separator itself is a return carriage or a line break.\",\"inLanguage\":\"en-US\"},\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to Split Delimited String into One Column in SQL Server ?","description":"To split delimited string with in SQL Server 2008, 2012 or 2014, use the XML functions in T-SQL queries to cut the text into one column.","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:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/","og_locale":"en_US","og_type":"article","og_title":"How to Split Delimited String into One Column in SQL Server ?","og_description":"To split delimited string with in SQL Server 2008, 2012 or 2014, use the XML functions in T-SQL queries to cut the text into one column.","og_url":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2024-02-29T06:02:00+00:00","article_modified_time":"2024-03-01T15:57:58+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/pattern_dark_texture-design-9D617D2FB8C_1920x1080.jpeg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"How to Split Delimited String into One Column in SQL Server ?","datePublished":"2024-02-29T06:02:00+00:00","dateModified":"2024-03-01T15:57:58+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/"},"wordCount":574,"commentCount":3,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/pattern_dark_texture-design-9D617D2FB8C_1920x1080.jpeg","articleSection":["T-SQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#respond"]}]},{"@type":["WebPage","FAQPage"],"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/","url":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/","name":"How to Split Delimited String into One Column in SQL Server ?","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/pattern_dark_texture-design-9D617D2FB8C_1920x1080.jpeg","datePublished":"2024-02-29T06:02:00+00:00","dateModified":"2024-03-01T15:57:58+00:00","description":"To split delimited string with in SQL Server 2008, 2012 or 2014, use the XML functions in T-SQL queries to cut the text into one column.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#breadcrumb"},"mainEntity":[{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550251408"},{"@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550329146"}],"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/pattern_dark_texture-design-9D617D2FB8C_1920x1080.jpeg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/08\/pattern_dark_texture-design-9D617D2FB8C_1920x1080.jpeg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"How to Split Delimited String into One Column in SQL Server ?"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}},{"@type":"Question","@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550251408","position":1,"url":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550251408","name":"What is a delimited text?","answerCount":1,"acceptedAnswer":{"@type":"Answer","text":"A delimited text is one unique line that has multiple parts. Each part is delimited with a specific symbol, for example, a semi-column, a tabulation, a vertical bar.","inLanguage":"en-US"},"inLanguage":"en-US"},{"@type":"Question","@id":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550329146","position":2,"url":"https:\/\/expert-only.com\/en\/t-sql\/how-to-split-delimited-string-into-one-column-in-sql-server\/#faq-question-1598550329146","name":"What is a text file with delimiters?","answerCount":1,"acceptedAnswer":{"@type":"Answer","text":"A delimited text file is a file that has multiple columns and every data contained on each line is delimited by a separator. For instance, the line separator itself is a return carriage or a line break.","inLanguage":"en-US"},"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/7019","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=7019"}],"version-history":[{"count":7,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/7019\/revisions"}],"predecessor-version":[{"id":30400,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/7019\/revisions\/30400"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/5789"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=7019"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=7019"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=7019"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}