The following function will take in a list, let’s say “AAA,BBB”, split them up to “AAA” and “BBB”, and allow the user to specify which one to return.<\/p>\n
\ncreate or replace function split(input_list varchar2, ret_this_one number, delimiter varchar2)\nreturn varchar2\nis\n\tv_list varchar2(32767) := delimiter || input_list;\n\tstart_position number;\n\tend_position number;\nbegin\n\tstart_position := instr(v_list, delimiter, 1, ret_this_one);\n\tif start_position > 0 then\n\t\tend_position := instr( v_list, delimiter, 1, ret_this_one + 1);\n\t\tif end_position = 0 then\n\t\t\tend_position := length(v_list) + 1; \n\t\tend if;\n\t\treturn(substr(v_list, start_position + 1, end_position - start_position - 1));\n\telse\n\t\treturn NULL;\n\tend if;\nend split;\n\/\nshow errors;\n<\/pre>\nIn the previous listed example, we would run this function as follows.<\/p>\n
\nselect split('AAA,BBB',1,','); -- Returns AAA\nselect split('AAA,BBB',2,','); -- Returns BBB\n<\/pre>\nPlease note that the first index is 1, not 0.<\/p>\n
This function is inspired and based on work done by Simon Baird<\/a> and Francois Degrelle<\/a>. Thanks Simon and Francois!<\/p>\n","protected":false},"excerpt":{"rendered":"
Some programming and scripting languages provide built-in functions that split a string around a given delimiter. Oracle SQL does not offer similar functionality out of the box, but this following function can help fill the gap.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-78","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/78","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=78"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/78\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=78"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=78"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=78"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}