{"id":3502,"date":"2024-08-12T13:20:56","date_gmt":"2024-08-12T05:20:56","guid":{"rendered":"https:\/\/five.co\/?p=3502"},"modified":"2025-04-15T16:29:57","modified_gmt":"2025-04-15T08:29:57","slug":"split-a-string-in-mysql","status":"publish","type":"post","link":"https:\/\/preview-php85.dzine.org\/five\/blog\/split-a-string-in-mysql\/","title":{"rendered":"Split a String In MySQL: How to Guide"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">How to Split a String In MySQL <\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/what-is-mysql\/\">MySQL<\/a> is a powerful relational database management system widely used for web applications, data analytics, and more. <\/p>\n\n\n\n<p class=\"wp-block-paragraph\">However, one common challenge developers encounter is splitting a string into multiple parts within a MySQL query. Unlike some other databases, MySQL doesn\u2019t have a built-in function to directly split a string. In this article, we\u2019ll explore how you can split strings in MySQL using various methods, along with examples to illustrate each approach.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\" \/>\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(25px + 0.390625vw);\"><strong>SQL Made Simple - Try Five<\/strong><br \/><span style=\"font-size: 14pt;\">Five is the Easiest Way to Create, Model and Query a SQL Database<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Started<\/strong><\/button><\/a><br \/><span style=\"color: #ffffff; font-size: revert;\">___<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"text-decoration: underline;\"><strong><span style=\"color: #ffffff; text-decoration: underline;\">Here&#8217;s What You Can Do With Five<\/span><\/strong><\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #ffffff;\">Create and Model a SQL Database \u2705<br \/>Write or Build SQL Queries \u2705<br \/>Visualize Queries as Charts or in Dashboards \u2705<br \/>Add CRUD Permissions to Control Data Access \u2705<br \/>Host Your Database Online \u2705\u00a0<\/span><\/p>\n<p style=\"text-align: center;\"><span style=\"color: #f1ebda; background-color: #4588d8;\"><em><strong>&#8220;Five bridges the gap between SQL and the web, <br \/>allowing me to create full-stack applications almost entirely in SQL&#8221;<\/strong><\/em><\/span><br \/><span style=\"color: #ffffff;\">&#8211; Crag Jones, Database Administrator (DBA)<\/span><\/p>\n<hr style=\"height: 5px;\" \/><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"root-eb-toc-dnd2e wp-block-essential-blocks-table-of-contents\"><div class=\"eb-parent-wrapper eb-parent-eb-toc-dnd2e \"><div class=\"eb-toc-container eb-toc-dnd2e  eb-toc-is-not-sticky eb-toc-not-collapsible eb-toc-initially-not-collapsed eb-toc-scrollToTop style-1 list-style-none\" data-scroll-top=\"false\" data-scroll-top-icon=\"fas fa-angle-up\" data-collapsible=\"false\" data-sticky-hide-mobile=\"false\" data-sticky=\"false\" data-scroll-target=\"scroll_to_toc\" data-copy-link=\"false\" data-editor-type=\"\" data-hide-desktop=\"false\" data-hide-tab=\"false\" data-hide-mobile=\"false\" data-itemcollapsed=\"false\" data-highlight-scroll=\"false\"><div class=\"eb-toc-header\"><h2 class=\"eb-toc-title\">Table of Contents<\/h2><\/div><div class=\"eb-toc-wrapper \" data-headers=\"[{&quot;level&quot;:2,&quot;content&quot;:&quot;How to Split a String In MySQL &quot;,&quot;text&quot;:&quot;How to Split a String In MySQL &quot;,&quot;link&quot;:&quot;how-to-split-a-string-in-mysql&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Introduction to String Splitting in MySQL&quot;,&quot;text&quot;:&quot;Introduction to String Splitting in MySQL&quot;,&quot;link&quot;:&quot;introduction-to-string-splitting-in-mysql&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Why You Might Need to Split Strings&quot;,&quot;text&quot;:&quot;Why You Might Need to Split Strings&quot;,&quot;link&quot;:&quot;why-you-might-need-to-split-strings&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Method 1: Using a Stored Procedure&quot;,&quot;text&quot;:&quot;Method 1: Using a Stored Procedure&quot;,&quot;link&quot;:&quot;method-1-using-a-stored-procedure&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Method 2: Using the SUBSTRING_INDEX()&quot;,&quot;text&quot;:&quot;Method 2: Using the SUBSTRING_INDEX()&quot;,&quot;link&quot;:&quot;method-2-using-the-substring_index&quot;},{&quot;level&quot;:4,&quot;content&quot;:&quot;How SUBSTRING_INDEX() Works&quot;,&quot;text&quot;:&quot;How SUBSTRING_INDEX() Works&quot;,&quot;link&quot;:&quot;how-substring_index-works&quot;},{&quot;level&quot;:4,&quot;content&quot;:&quot;Examples of SUBSTRING_INDEX()&quot;,&quot;text&quot;:&quot;Examples of SUBSTRING_INDEX()&quot;,&quot;link&quot;:&quot;examples-of-substring_index&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Method 3: Using a Recursive Common Table Expression (CTE)&quot;,&quot;text&quot;:&quot;Method 3: Using a Recursive Common Table Expression (CTE)&quot;,&quot;link&quot;:&quot;method-3-using-a-recursive-common-table-expression-cte&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Method 4: Using MySQL 8.0&apos;s JSON Functions&quot;,&quot;text&quot;:&quot;Method 4: Using MySQL 8.0&apos;s JSON Functions&quot;,&quot;link&quot;:&quot;method-4-using-mysql-80s-json-functions&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Examples and Use Cases&quot;,&quot;text&quot;:&quot;Examples and Use Cases&quot;,&quot;link&quot;:&quot;examples-and-use-cases&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Example 1: Splitting Comma-Separated Tags&quot;,&quot;text&quot;:&quot;Example 1: Splitting Comma-Separated Tags&quot;,&quot;link&quot;:&quot;example-1-splitting-comma-separated-tags&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Example 2: Parsing a List of Items&quot;,&quot;text&quot;:&quot;Example 2: Parsing a List of Items&quot;,&quot;link&quot;:&quot;example-2-parsing-a-list-of-items&quot;},{&quot;level&quot;:3,&quot;content&quot;:&quot;Other Things To Note&quot;,&quot;text&quot;:&quot;Other Things To Note&quot;,&quot;link&quot;:&quot;other-things-to-note&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Frequently Asked Questions (FAQ)&quot;,&quot;text&quot;:&quot;Frequently Asked Questions (FAQ)&quot;,&quot;link&quot;:&quot;frequently-asked-questions-faq&quot;},{&quot;level&quot;:2,&quot;content&quot;:&quot;Summary: Split a String In MySQL&quot;,&quot;text&quot;:&quot;Summary: Split a String In MySQL&quot;,&quot;link&quot;:&quot;summary-split-a-string-in-mysql&quot;}]\" data-visible=\"[true,true,true,true,true,true]\" data-delete-headers=\"[{&quot;label&quot;:&quot;How to Split a String In MySQL &quot;,&quot;value&quot;:&quot;how-to-split-a-string-in-mysql&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Introduction to String Splitting in MySQL&quot;,&quot;value&quot;:&quot;introduction-to-string-splitting-in-mysql&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Why You Might Need to Split Strings&quot;,&quot;value&quot;:&quot;why-you-might-need-to-split-strings&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Method 1: Using a Stored Procedure&quot;,&quot;value&quot;:&quot;method-1-using-a-stored-procedure&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Method 2: Using the SUBSTRING_INDEX()&quot;,&quot;value&quot;:&quot;method-2-using-the-substring_index&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;How SUBSTRING_INDEX() Works&quot;,&quot;value&quot;:&quot;how-substring_index-works&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Examples of SUBSTRING_INDEX()&quot;,&quot;value&quot;:&quot;examples-of-substring_index&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Method 3: Using a Recursive Common Table Expression (CTE)&quot;,&quot;value&quot;:&quot;method-3-using-a-recursive-common-table-expression-cte&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Method 4: Using MySQL 8.0&apos;s JSON Functions&quot;,&quot;value&quot;:&quot;method-4-using-mysql-80s-json-functions&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Examples and Use Cases&quot;,&quot;value&quot;:&quot;examples-and-use-cases&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Example 1: Splitting Comma-Separated Tags&quot;,&quot;value&quot;:&quot;example-1-splitting-comma-separated-tags&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Example 2: Parsing a List of Items&quot;,&quot;value&quot;:&quot;example-2-parsing-a-list-of-items&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Other Things To Note&quot;,&quot;value&quot;:&quot;other-things-to-note&quot;,&quot;isDelete&quot;:true},{&quot;label&quot;:&quot;Frequently Asked Questions (FAQ)&quot;,&quot;value&quot;:&quot;frequently-asked-questions-faq&quot;,&quot;isDelete&quot;:false},{&quot;label&quot;:&quot;Summary: Split a String In MySQL&quot;,&quot;value&quot;:&quot;summary-split-a-string-in-mysql&quot;,&quot;isDelete&quot;:false}]\" data-smooth=\"true\" data-top-offset=\"\"><div class=\"eb-toc__list-wrap\"><ul class=\"eb-toc__list\"><li><a href=\"#how-to-split-a-string-in-mysql\">How to Split a String In MySQL <\/a><li><a href=\"#introduction-to-string-splitting-in-mysql\">Introduction to String Splitting in MySQL<\/a><li><a href=\"#why-you-might-need-to-split-strings\">Why You Might Need to Split Strings<\/a><li><a href=\"#method-1-using-a-stored-procedure\">Method 1: Using a Stored Procedure<\/a><li><a href=\"#method-2-using-the-substring_index\">Method 2: Using the SUBSTRING_INDEX()<\/a><li><a href=\"#method-3-using-a-recursive-common-table-expression-cte\">Method 3: Using a Recursive Common Table Expression (CTE)<\/a><li><a href=\"#method-4-using-mysql-80s-json-functions\">Method 4: Using MySQL 8.0&#8217;s JSON Functions<\/a><li><a href=\"#frequently-asked-questions-faq\">Frequently Asked Questions (FAQ)<\/a><li><a href=\"#summary-split-a-string-in-mysql\">Summary: Split a String In MySQL<\/a><\/ul><\/div><\/div><\/div><\/div><\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to String Splitting in MySQL<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">String splitting is a common requirement in data processing, especially when dealing with delimited data stored in a single column. For instance, you might have a list of comma-separated values (CSV) in a column and need to break them into individual components.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">While <a href=\"https:\/\/preview-php85.dzine.org\/five\/blog\/how-to-create-a-front-end-for-a-mysql-database\/\">MySQL<\/a> doesn\u2019t provide a built-in <code>SPLIT<\/code> function, there are several workarounds to achieve the same effect. Depending on your MySQL version and specific use case, you can choose from different methods to split strings.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Why You Might Need to Split Strings<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">There are several scenarios where splitting strings in MySQL is necessary:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Parsing CSV Data<\/strong>: When data is stored in a single column as comma-separated values.<\/li>\n\n\n\n<li><strong>Breaking Down Tags or Keywords<\/strong>: When a list of tags or keywords needs to be stored in individual rows.<\/li>\n\n\n\n<li><strong>Data Normalization<\/strong>: When denormalized data needs to be split and moved into separate rows in a related table.<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Splitting strings allows you to perform more detailed analysis and ensures your data is structured in a more manageable and efficient way.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Method 1: Using a Stored Procedure<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">One way to split a string in MySQL is by creating a stored procedure. Stored procedures are routines that are stored in the database and can be invoked with parameters. Here\u2019s an example of how you can create a stored procedure to split a string:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"DELIMITER $$\n\nCREATE PROCEDURE SplitString(\n    IN input_string VARCHAR(255),\n    IN delimiter CHAR(1)\n)\nBEGIN\n    DECLARE pos INT DEFAULT 1;\n    DECLARE str VARCHAR(255);\n\n    CREATE TEMPORARY TABLE temp_strings (str VARCHAR(255));\n\n    REPEAT\n        SET str = SUBSTRING_INDEX(SUBSTRING_INDEX(input_string, delimiter, pos), delimiter, -1);\n        IF str != '' THEN\n            INSERT INTO temp_strings (str) VALUES (str);\n        END IF;\n        SET pos = pos + 1;\n    UNTIL str = '' END REPEAT;\n\n    SELECT * FROM temp_strings;\n    DROP TEMPORARY TABLE temp_strings;\nEND$$\n\nDELIMITER ;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">DELIMITER $$<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">PROCEDURE<\/span><span style=\"color: #ABB2BF\"> SplitString(<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">IN<\/span><span style=\"color: #ABB2BF\"> input_string <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">255<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">IN<\/span><span style=\"color: #ABB2BF\"> delimiter <\/span><span style=\"color: #C678DD\">CHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">BEGIN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> pos <\/span><span style=\"color: #C678DD\">INT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">DEFAULT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DECLARE<\/span><span style=\"color: #ABB2BF\"> str <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">255<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">CREATE<\/span><span style=\"color: #ABB2BF\"> TEMPORARY <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> temp_strings (str <\/span><span style=\"color: #C678DD\">VARCHAR<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #D19A66\">255<\/span><span style=\"color: #ABB2BF\">));<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">REPEAT<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> str <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> SUBSTRING_INDEX(SUBSTRING_INDEX(input_string, delimiter, pos), delimiter, -<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">IF<\/span><span style=\"color: #ABB2BF\"> str <\/span><span style=\"color: #56B6C2\">!=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">THEN<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">            <\/span><span style=\"color: #C678DD\">INSERT INTO<\/span><span style=\"color: #ABB2BF\"> temp_strings (str) <\/span><span style=\"color: #C678DD\">VALUES<\/span><span style=\"color: #ABB2BF\"> (str);<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">IF<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> pos <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> pos + <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    UNTIL str <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;&#39;<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">REPEAT<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> * <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> temp_strings;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">DROP<\/span><span style=\"color: #ABB2BF\"> TEMPORARY <\/span><span style=\"color: #C678DD\">TABLE<\/span><span style=\"color: #ABB2BF\"> temp_strings;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">END<\/span><span style=\"color: #ABB2BF\">$$<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">DELIMITER ;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This stored procedure accepts an input string and a delimiter, splits the string by the delimiter, and stores the resulting parts in a temporary table. You can call this procedure as follows:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"CALL SplitString('apple,orange,banana', ',');\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">CALL<\/span><span style=\"color: #ABB2BF\"> SplitString(<\/span><span style=\"color: #98C379\">&#39;apple,orange,banana&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">);<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"has-text-align-center wp-block-paragraph\"><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-vivid-cyan-blue-color\"><strong style=\"background-color: rgb(69, 136, 216); color: rgb(241, 235, 218); font-size: calc(12px + 0.390625vw); font-weight: bold;\">Split a String In MySQL with a Rapid Database Builder<\/strong><\/mark><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">When working with MySQL, efficiently handling string manipulation, such as splitting strings, is essential for database management.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five is an online database builder meaning you can easily define your MySQL database schema. Five simplifies the process of splitting and managing strings in MySQL by generating an automatic UI, making it easier to interact with and manipulate your data.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Five\u2019s interface allows you to create forms, charts, and reports that can include string-based operations, helping you manage data that contains delimited values effectively.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Moreover, Five enables you to write custom JavaScript and TypeScript functions, giving you the flexibility to implement complex string-related logic. This is particularly useful for applications that require advanced string manipulation, such as extracting specific parts of a string or separating values based on delimiters like commas or semicolons.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Once your application is ready, you can deploy it to a secure, scalable cloud infrastructure with just a few clicks, allowing you to focus on developing your application without worrying about the complexities of deployment.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If you\u2019re serious about working with MySQL, give Five a try. <a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\">Sign up for free access<\/a> to Five\u2019s online development environment and start building your web application\/database today.<\/p>\n\n\n\n<div style=\"background-color: #001524;\"><hr style=\"height: 5px;\">\n<pre style=\"text-align: center; overflow: hidden; white-space: pre-line;\"><span style=\"color: #f1ebda; background-color: #4588d8; font-size: calc(18px + 0.390625vw);\"><strong>Build Your Database In 3 Steps<\/strong><br><span style=\"font-size: 14pt;\">Start Developing Today<\/span><\/span><\/pre>\n<p style=\"text-align: center;\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\" target=\"_blank\" rel=\"noopener\"><button style=\"background-color: #f8b92b; border: none; color: black; padding: 20px; text-align: center; text-decoration: none; display: inline-block; font-size: 18px; cursor: pointer; margin: 4px 2px; border-radius: 5px;\"><strong>Get Instant Access<\/strong><\/button><br><\/a><\/p>\n<hr style=\"height: 5px;\"><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter\"><a href=\"https:\/\/preview-php85.dzine.org\/five\/get-started\/\"><img src=\"https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Untitled-design-12-1024x576-1.gif\" alt=\"An example application built on a MySQL database using Five\"\/><\/a><figcaption class=\"wp-element-caption\">An example application built on a MySQL database <strong>using Five<\/strong><\/figcaption><\/figure>\n<\/div>\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">Now, let&#8217;s dive back into the next way to split a string in MySQL<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Method 2: Using the <code>SUBSTRING_INDEX()<\/code><\/h2>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>How <code>SUBSTRING_INDEX()<\/code> Works<\/strong><\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">The <code>SUBSTRING_INDEX()<\/code> function returns a substring from a string before a specified number of occurrences of a delimiter. The function\u2019s syntax is as follows:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SUBSTRING_INDEX(string, delimiter, count)\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">SUBSTRING_INDEX(string, delimiter, count)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong><code>string<\/code><\/strong>: The original string from which you want to extract a substring.<\/li>\n\n\n\n<li><strong><code>delimiter<\/code><\/strong>: The delimiter that is used to identify where the string should be split.<\/li>\n\n\n\n<li><strong><code>count<\/code><\/strong>: An integer indicating the number of times the delimiter should be encountered. If <code>count<\/code> is positive, the function returns the substring before the <code>count<\/code> occurrence of the delimiter. If <code>count<\/code> is negative, it returns the substring after the <code>count<\/code> occurrence of the delimiter.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\"><strong>Examples of <code>SUBSTRING_INDEX()<\/code><\/strong><\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Extracting the First Segment of a Delimited String<\/strong> Suppose you have a comma-separated string and you want to extract the first element:<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"   SELECT SUBSTRING_INDEX('apple,orange,banana', ',', 1) AS first_part;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> SUBSTRING_INDEX(<\/span><span style=\"color: #98C379\">&#39;apple,orange,banana&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> first_part;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Result:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"   first_part\n   -----------\n   apple\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   first_part<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #7F848E; font-style: italic\">-----------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   apple<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">Here, the function returns &#8216;apple&#8217;, which is the first element before the first comma.<\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"2\">\n<li><strong>Extracting the First Two Segments of a Delimited String<\/strong> If you want to extract the first two segments from the string:<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"   SELECT SUBSTRING_INDEX('apple,orange,banana', ',', 2) AS first_two_parts;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> SUBSTRING_INDEX(<\/span><span style=\"color: #98C379\">&#39;apple,orange,banana&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> first_two_parts;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Result:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"   first_two_parts\n   -----------------\n   apple,orange\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   first_two_parts<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #7F848E; font-style: italic\">-----------------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   apple,orange<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">In this example, the function returns &#8216;apple,orange&#8217;, which includes everything before the second comma.<\/p>\n\n\n\n<ol class=\"wp-block-list\" start=\"3\">\n<li><strong>Extracting the Last Segment of a Delimited String<\/strong> You can also use a negative count to extract the last segment after the last occurrence of the delimiter:<\/li>\n<\/ol>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"   SELECT SUBSTRING_INDEX('apple,orange,banana', ',', -1) AS last_part;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> SUBSTRING_INDEX(<\/span><span style=\"color: #98C379\">&#39;apple,orange,banana&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">, -<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> last_part;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Result:<\/strong><\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"   last_part\n   -----------\n   banana\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #ABB2BF\">   last_part<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   <\/span><span style=\"color: #7F848E; font-style: italic\">-----------<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">   banana<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This returns &#8216;banana&#8217;, which is the segment after the last comma in the string.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">While <code>SUBSTRING_INDEX()<\/code> is useful for extracting specific segments of a string, it is not a full-fledged string splitting function. It only allows you to work with one segment at a time. For more complex string splitting tasks, such as turning a delimited string into multiple rows, you may need to use stored procedures, recursive CTEs, or other techniques.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Method 3: Using a Recursive Common Table Expression (CTE)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">For users of MySQL 8.0 and later, Recursive CTEs provide a powerful way to split strings without the need for stored procedures. Here\u2019s an example:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(2 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"WITH RECURSIVE split_string_cte AS (\n    SELECT \n        SUBSTRING_INDEX('apple,orange,banana', ',', 1) AS value,\n        SUBSTRING_INDEX('apple,orange,banana', ',', -1) AS rest,\n        1 AS level\n    UNION ALL\n    SELECT \n        SUBSTRING_INDEX(rest, ',', 1),\n        IF(INSTR(rest, ',') = 0, '', SUBSTRING_INDEX(rest, ',', -1)),\n        level + 1\n    FROM split_string_cte\n    WHERE rest != ''\n)\nSELECT value FROM split_string_cte;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">WITH<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">RECURSIVE<\/span><span style=\"color: #ABB2BF\"> split_string_cte <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> (<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        SUBSTRING_INDEX(<\/span><span style=\"color: #98C379\">&#39;apple,orange,banana&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">value<\/span><span style=\"color: #ABB2BF\">,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        SUBSTRING_INDEX(<\/span><span style=\"color: #98C379\">&#39;apple,orange,banana&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">, -<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> rest,<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">level<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        SUBSTRING_INDEX(rest, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">IF<\/span><span style=\"color: #ABB2BF\">(INSTR(rest, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #98C379\">&#39;&#39;<\/span><span style=\"color: #ABB2BF\">, SUBSTRING_INDEX(rest, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">, -<\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\">)),<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">        <\/span><span style=\"color: #C678DD\">level<\/span><span style=\"color: #ABB2BF\"> + <\/span><span style=\"color: #D19A66\">1<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> split_string_cte<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">    <\/span><span style=\"color: #C678DD\">WHERE<\/span><span style=\"color: #ABB2BF\"> rest <\/span><span style=\"color: #56B6C2\">!=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;&#39;<\/span><\/span>\n<span class=\"line\"><span style=\"color: #ABB2BF\">)<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">value<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> split_string_cte;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This query splits the string recursively by the delimiter and selects each part as a separate row.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Method 4: Using MySQL 8.0&#8217;s JSON Functions<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Another feature in MySQL 8.0 is JSON support, which you can use creatively to split strings:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SET @json_string = '[\\&quot;apple\\&quot;,\\&quot;orange\\&quot;,\\&quot;banana\\&quot;]';\n\nSELECT json_extract(@json_string, CONCAT('$[', idx, ']')) AS value\nFROM (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2) AS indices;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SET<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #E06C75\">@json_string<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #56B6C2\">=<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #98C379\">&#39;[\\&quot;apple\\&quot;,\\&quot;orange\\&quot;,\\&quot;banana\\&quot;]&#39;<\/span><span style=\"color: #ABB2BF\">;<\/span><\/span>\n<span class=\"line\"><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> json_extract(<\/span><span style=\"color: #E06C75\">@json_string<\/span><span style=\"color: #ABB2BF\">, <\/span><span style=\"color: #56B6C2\">CONCAT<\/span><span style=\"color: #ABB2BF\">(<\/span><span style=\"color: #98C379\">&#39;$[&#39;<\/span><span style=\"color: #ABB2BF\">, idx, <\/span><span style=\"color: #98C379\">&#39;]&#39;<\/span><span style=\"color: #ABB2BF\">)) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">value<\/span><\/span>\n<span class=\"line\"><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> (<\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">0<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> idx <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">1<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">UNION ALL<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> <\/span><span style=\"color: #D19A66\">2<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">AS<\/span><span style=\"color: #ABB2BF\"> indices;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<p class=\"wp-block-paragraph\">This approach works by treating the string as a JSON array and extracting elements based on their index.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Examples and Use Cases<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Example 1: Splitting Comma-Separated Tags<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">If you have a column storing tags as a comma-separated string:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT SplitString(tags, ',') FROM articles;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> SplitString(tags, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> articles;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<h3 class=\"wp-block-heading\">Example 2: Parsing a List of Items<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">For a list of items in a single column:<\/p>\n\n\n\n<div class=\"wp-block-kevinbatdorf-code-block-pro cbp-has-line-numbers\" data-code-block-pro-font-family=\"Code-Pro-JetBrains-Mono\" style=\"font-size:.875rem;font-family:Code-Pro-JetBrains-Mono,ui-monospace,SFMono-Regular,Menlo,Monaco,Consolas,monospace;--cbp-line-number-color:#abb2bf;--cbp-line-number-width:calc(1 * 0.6 * .875rem);line-height:1.25rem;--cbp-tab-width:2;tab-size:var(--cbp-tab-width, 2)\"><span style=\"display:block;padding:16px 0 0 16px;margin-bottom:-1px;width:100%;text-align:left;background-color:#282c34\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"54\" height=\"14\" viewBox=\"0 0 54 14\"><g fill=\"none\" fill-rule=\"evenodd\" transform=\"translate(1 1)\"><circle cx=\"6\" cy=\"6\" r=\"6\" fill=\"#FF5F56\" stroke=\"#E0443E\" stroke-width=\".5\"><\/circle><circle cx=\"26\" cy=\"6\" r=\"6\" fill=\"#FFBD2E\" stroke=\"#DEA123\" stroke-width=\".5\"><\/circle><circle cx=\"46\" cy=\"6\" r=\"6\" fill=\"#27C93F\" stroke=\"#1AAB29\" stroke-width=\".5\"><\/circle><\/g><\/svg><\/span><span role=\"button\" tabindex=\"0\" data-code=\"SELECT SplitString(item_list, ',') FROM orders;\" style=\"color:#abb2bf;display:none\" aria-label=\"Copy\" class=\"code-block-pro-copy-button\"><svg xmlns=\"http:\/\/www.w3.org\/2000\/svg\" style=\"width:24px;height:24px\" fill=\"none\" viewBox=\"0 0 24 24\" stroke=\"currentColor\" stroke-width=\"2\"><path class=\"with-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2m-6 9l2 2 4-4\"><\/path><path class=\"without-check\" stroke-linecap=\"round\" stroke-linejoin=\"round\" d=\"M9 5H7a2 2 0 00-2 2v12a2 2 0 002 2h10a2 2 0 002-2V7a2 2 0 00-2-2h-2M9 5a2 2 0 002 2h2a2 2 0 002-2M9 5a2 2 0 012-2h2a2 2 0 012 2\"><\/path><\/svg><\/span><pre class=\"shiki one-dark-pro\" style=\"background-color: #282c34\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color: #C678DD\">SELECT<\/span><span style=\"color: #ABB2BF\"> SplitString(item_list, <\/span><span style=\"color: #98C379\">&#39;,&#39;<\/span><span style=\"color: #ABB2BF\">) <\/span><span style=\"color: #C678DD\">FROM<\/span><span style=\"color: #ABB2BF\"> orders;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n<div style=\"height:20px\" aria-hidden=\"true\" class=\"wp-block-spacer\"><\/div>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Other Things To Note<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">When splitting strings in MySQL, especially with large datasets, performance can be a concern. Recursive CTEs and stored procedures can be resource-intensive, so it\u2019s important to test these methods with your data and optimize queries as needed.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">If performance becomes an issue, consider preprocessing the data at the application level before inserting it into the database.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Frequently Asked Questions (FAQ)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q1: Can I split strings in MySQL without using stored procedures?<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Yes, you can use Recursive CTEs or JSON functions in MySQL 8.0 and later.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q2: What is the best method for splitting strings?<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">The best method depends on your specific use case and the MySQL version you\u2019re using. For simple cases, a stored procedure might suffice, while more complex scenarios might benefit from Recursive CTEs or JSON functions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Q3: How can I handle performance issues when splitting strings?<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Consider optimizing your queries, reducing the complexity of your splits, or preprocessing data at the application level.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Summary: Split a String In MySQL<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Splitting strings in MySQL may not be straightforward due to the lack of a built-in function, but with the methods outlined in this article\u2014using stored procedures, Recursive CTEs, and JSON functions\u2014you can effectively manage and manipulate string data within your MySQL database.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>How to Split a String In MySQL MySQL is a powerful relational database management system widely used for web applications, data analytics, and more. However, one common challenge developers encounter is splitting a string into multiple parts within a MySQL query. Unlike some other databases, MySQL doesn\u2019t have a built-in function to directly split a [&hellip;]<\/p>\n","protected":false},"author":11,"featured_media":3503,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_eb_attr":"","_uag_custom_page_level_css":"","footnotes":""},"categories":[21],"tags":[],"class_list":["post-3502","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blog"],"acf":[],"uagb_featured_image_src":{"full":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Split-a-String-In-MySQL-How-to-Guide.png",1280,720,false],"thumbnail":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Split-a-String-In-MySQL-How-to-Guide-150x150.png",150,150,true],"medium":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Split-a-String-In-MySQL-How-to-Guide-300x169.png",300,169,true],"medium_large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Split-a-String-In-MySQL-How-to-Guide-768x432.png",768,432,true],"large":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Split-a-String-In-MySQL-How-to-Guide-1024x576.png",1024,576,true],"1536x1536":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Split-a-String-In-MySQL-How-to-Guide.png",1280,720,false],"2048x2048":["https:\/\/preview-php85.dzine.org\/five\/wp-content\/uploads\/2024\/08\/Five.Co-Split-a-String-In-MySQL-How-to-Guide.png",1280,720,false]},"uagb_author_info":{"display_name":"Ryan Forrester","author_link":"https:\/\/preview-php85.dzine.org\/five\/author\/f1v5_ed9_au\/"},"uagb_comment_info":0,"uagb_excerpt":"How to Split a String In MySQL MySQL is a powerful relational database management system widely used for web applications, data analytics, and more. However, one common challenge developers encounter is splitting a string into multiple parts within a MySQL query. Unlike some other databases, MySQL doesn\u2019t have a built-in function to directly split a&hellip;","_links":{"self":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/3502","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/users\/11"}],"replies":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/comments?post=3502"}],"version-history":[{"count":0,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/posts\/3502\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media\/3503"}],"wp:attachment":[{"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/media?parent=3502"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/categories?post=3502"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/preview-php85.dzine.org\/five\/wp-json\/wp\/v2\/tags?post=3502"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}