{"id":19663,"date":"2024-03-15T19:16:55","date_gmt":"2024-03-15T19:16:55","guid":{"rendered":"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/"},"modified":"2024-03-21T16:14:24","modified_gmt":"2024-03-21T16:14:24","slug":"how-can-oracle-extract-a-substring-based-on-a-comma","status":"publish","type":"post","link":"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/","title":{"rendered":"How can Oracle extract a substring based on a comma?"},"content":{"rendered":"<p>In Oracle, you can use the SUBSTR function and INSTR function to extract a string based on commas.<\/p>\n<p>Here is an example:<\/p>\n<p>Assuming there is a string like this:<br \/>\nstr := &#8216;apple,banana,orange&#8217;<\/p>\n<p>You can use the following code to split a string based on commas:<\/p>\n<pre class=\"post-pre\"><code>-- \u622a\u53d6\u7b2c\u4e00\u4e2a\u9017\u53f7\u4e4b\u524d\u7684\u5b57\u7b26\u4e32\r\nSELECT SUBSTR(str, 1, INSTR(str, ',')-1) FROM dual;\r\n\r\n-- \u8f93\u51fa\u7ed3\u679c\u4e3a 'apple'\r\n\r\n-- \u622a\u53d6\u7b2c\u4e00\u4e2a\u9017\u53f7\u4e4b\u540e\u7684\u5b57\u7b26\u4e32\r\nSELECT SUBSTR(str, INSTR(str, ',')+1) FROM dual;\r\n\r\n-- \u8f93\u51fa\u7ed3\u679c\u4e3a 'banana,orange'\r\n\r\n-- \u622a\u53d6\u7b2c\u4e8c\u4e2a\u9017\u53f7\u4e4b\u524d\u7684\u5b57\u7b26\u4e32\r\nSELECT SUBSTR(str, INSTR(str, ',', 1, 2)-1) FROM dual;\r\n\r\n-- \u8f93\u51fa\u7ed3\u679c\u4e3a 'banana'\r\n\r\n-- \u622a\u53d6\u7b2c\u4e8c\u4e2a\u9017\u53f7\u4e4b\u540e\u7684\u5b57\u7b26\u4e32\r\nSELECT SUBSTR(str, INSTR(str, ',', 1, 2)+1) FROM dual;\r\n\r\n-- \u8f93\u51fa\u7ed3\u679c\u4e3a 'orange'\r\n<\/code><\/pre>\n<p>The SUBSTR function can be used to specify the starting position and length of the substring to extract. The INSTR function is used to locate the position of a comma.<\/p>\n<p>It is worth noting that &#8216;dual&#8217; in the above code is a virtual table used for querying when there is no actual table present. You can replace it with the name of the table you want to query based on the actual situation.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle, you can use the SUBSTR function and INSTR function to extract a string based on commas. Here is an example: Assuming there is a string like this: str := &#8216;apple,banana,orange&#8217; You can use the following code to split a string based on commas: &#8212; \u622a\u53d6\u7b2c\u4e00\u4e2a\u9017\u53f7\u4e4b\u524d\u7684\u5b57\u7b26\u4e32 SELECT SUBSTR(str, 1, INSTR(str, &#8216;,&#8217;)-1) FROM dual; &#8212; [&hellip;]<\/p>\n","protected":false},"author":10,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_import_markdown_pro_load_document_selector":0,"_import_markdown_pro_submit_text_textarea":"","footnotes":""},"categories":[1],"tags":[],"class_list":["post-19663","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.5 (Yoast SEO v21.5) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>How can Oracle extract a substring based on a comma? - Blog - Silicon Cloud<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How can Oracle extract a substring based on a comma?\" \/>\n<meta property=\"og:description\" content=\"In Oracle, you can use the SUBSTR function and INSTR function to extract a string based on commas. Here is an example: Assuming there is a string like this: str := &#8216;apple,banana,orange&#8217; You can use the following code to split a string based on commas: -- \u622a\u53d6\u7b2c\u4e00\u4e2a\u9017\u53f7\u4e4b\u524d\u7684\u5b57\u7b26\u4e32 SELECT SUBSTR(str, 1, INSTR(str, &#039;,&#039;)-1) FROM dual; -- [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\" \/>\n<meta property=\"article:published_time\" content=\"2024-03-15T19:16:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-21T16:14:24+00:00\" \/>\n<meta name=\"author\" content=\"Jackson Davis\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:site\" content=\"@SiliCloudGlobal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Jackson Davis\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/\"},\"author\":{\"name\":\"Jackson Davis\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/55a10b8b0457c35884c25677889ad350\"},\"headline\":\"How can Oracle extract a substring based on a comma?\",\"datePublished\":\"2024-03-15T19:16:55+00:00\",\"dateModified\":\"2024-03-21T16:14:24+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/\"},\"wordCount\":129,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/\",\"name\":\"How can Oracle extract a substring based on a comma? - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\"},\"datePublished\":\"2024-03-15T19:16:55+00:00\",\"dateModified\":\"2024-03-21T16:14:24+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.silicloud.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How can Oracle extract a substring based on a comma?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"name\":\"Silicon Cloud Blog\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\"},\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#organization\",\"name\":\"Silicon Cloud Blog\",\"url\":\"https:\/\/www.silicloud.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"contentUrl\":\"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png\",\"width\":1024,\"height\":1024,\"caption\":\"Silicon Cloud Blog\"},\"image\":{\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/SiliCloudGlobal\/\",\"https:\/\/twitter.com\/SiliCloudGlobal\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/55a10b8b0457c35884c25677889ad350\",\"name\":\"Jackson Davis\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/2fdb47d6df1226e92380d96973782572a97b0675d098bb914410dec348eb5d29?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/2fdb47d6df1226e92380d96973782572a97b0675d098bb914410dec348eb5d29?s=96&d=mm&r=g\",\"caption\":\"Jackson Davis\"},\"url\":\"https:\/\/www.silicloud.com\/blog\/author\/jacksondavis\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How can Oracle extract a substring based on a comma? - Blog - Silicon Cloud","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/","og_locale":"en_US","og_type":"article","og_title":"How can Oracle extract a substring based on a comma?","og_description":"In Oracle, you can use the SUBSTR function and INSTR function to extract a string based on commas. Here is an example: Assuming there is a string like this: str := &#8216;apple,banana,orange&#8217; You can use the following code to split a string based on commas: -- \u622a\u53d6\u7b2c\u4e00\u4e2a\u9017\u53f7\u4e4b\u524d\u7684\u5b57\u7b26\u4e32 SELECT SUBSTR(str, 1, INSTR(str, ',')-1) FROM dual; -- [&hellip;]","og_url":"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/","og_site_name":"Blog - Silicon Cloud","article_publisher":"https:\/\/www.facebook.com\/SiliCloudGlobal\/","article_published_time":"2024-03-15T19:16:55+00:00","article_modified_time":"2024-03-21T16:14:24+00:00","author":"Jackson Davis","twitter_card":"summary_large_image","twitter_creator":"@SiliCloudGlobal","twitter_site":"@SiliCloudGlobal","twitter_misc":{"Written by":"Jackson Davis","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/#article","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/"},"author":{"name":"Jackson Davis","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/55a10b8b0457c35884c25677889ad350"},"headline":"How can Oracle extract a substring based on a comma?","datePublished":"2024-03-15T19:16:55+00:00","dateModified":"2024-03-21T16:14:24+00:00","mainEntityOfPage":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/"},"wordCount":129,"commentCount":0,"publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/","url":"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/","name":"How can Oracle extract a substring based on a comma? - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/blog\/#website"},"datePublished":"2024-03-15T19:16:55+00:00","dateModified":"2024-03-21T16:14:24+00:00","breadcrumb":{"@id":"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/blog\/how-can-oracle-extract-a-substring-based-on-a-comma\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.silicloud.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How can Oracle extract a substring based on a comma?"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/blog\/#website","url":"https:\/\/www.silicloud.com\/blog\/","name":"Silicon Cloud Blog","description":"","publisher":{"@id":"https:\/\/www.silicloud.com\/blog\/#organization"},"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.silicloud.com\/blog\/#organization","name":"Silicon Cloud Blog","url":"https:\/\/www.silicloud.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","contentUrl":"https:\/\/www.silicloud.com\/blog\/wp-content\/uploads\/2023\/11\/EN-SILICON-Full.png","width":1024,"height":1024,"caption":"Silicon Cloud Blog"},"image":{"@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/SiliCloudGlobal\/","https:\/\/twitter.com\/SiliCloudGlobal"]},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/55a10b8b0457c35884c25677889ad350","name":"Jackson Davis","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.silicloud.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/2fdb47d6df1226e92380d96973782572a97b0675d098bb914410dec348eb5d29?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/2fdb47d6df1226e92380d96973782572a97b0675d098bb914410dec348eb5d29?s=96&d=mm&r=g","caption":"Jackson Davis"},"url":"https:\/\/www.silicloud.com\/blog\/author\/jacksondavis\/"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/19663","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/users\/10"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/comments?post=19663"}],"version-history":[{"count":1,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/19663\/revisions"}],"predecessor-version":[{"id":53417,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/posts\/19663\/revisions\/53417"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/media?parent=19663"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/categories?post=19663"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/blog\/wp-json\/wp\/v2\/tags?post=19663"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}