{"id":1673,"date":"2018-08-15T07:58:07","date_gmt":"2018-08-15T07:58:07","guid":{"rendered":"https:\/\/vinish.dev\/?p=1673"},"modified":"2025-04-16T19:49:15","modified_gmt":"2025-04-16T14:19:15","slug":"how-to-split-a-string-in-pl-sql","status":"publish","type":"post","link":"https:\/\/vinish.dev\/how-to-split-a-string-in-pl-sql","title":{"rendered":"How to Split a String in PL\/SQL?"},"content":{"rendered":"

In this article, I am giving an example of a function which I mostly use to split a string in PL\/SQL. Especially, when I am writing a program to import a delimited file. Here are that function and an example of how to use it.<\/p>\n

PL\/SQL Function to Split a String<\/h3>\n

This function takes four parameters, and the details of these parameters are as under:<\/p>\n

    \n
  1. Source_String<\/strong>: String to Split<\/li>\n
  2. Field_Position<\/strong>: Delimiter Position<\/li>\n
  3. UnTerminated<\/strong>: False by default, but if you pass True, then it will add the specified delimiter at the end of the string.<\/li>\n
  4. Delimiter<\/strong>: Default is Comma ',', you can pass any other delimiter.<\/li>\n<\/ol>\n
    CREATE OR REPLACE FUNCTION GetString (\r\nSource_string IN VARCHAR2,\r\nField_position IN NUMBER,\r\nUnTerminated IN BOOLEAN DEFAULT FALSE,\r\nDelimiter IN VARCHAR2 DEFAULT ',')\r\nRETURN VARCHAR2\r\nIS\r\niPtrEnd PLS_INTEGER := 0;\r\niPtrStart PLS_INTEGER := 0;\r\nvcSourceStrCopy VARCHAR2 (4000) := Source_string;\r\nBEGIN\r\nIF UnTerminated\r\nTHEN\r\nvcSourceStrCopy := vcSourceStrCopy || Delimiter;\r\nEND IF;\r\n\r\nIF Field_Position > 1\r\nTHEN\r\niPtrStart :=\r\nINSTR (vcSourceStrCopy,\r\nDelimiter,\r\n1,\r\nField_Position - 1)\r\n+ LENGTH (Delimiter);\r\nELSE\r\niPtrStart := 1;\r\nEND IF;\r\n\r\niPtrEnd :=\r\nINSTR (vcSourceStrCopy,\r\nDelimiter,\r\n1,\r\nField_Position);\r\nRETURN SUBSTR (vcSourceStrCopy, iPtrStart, (iPtrEnd - iPtrStart));\r\nEND GetString;<\/pre>\n
    Example: Use This Function in PL\/SQL Program<\/h3>\n
    SET SERVEROUTPUT ON;\r\n\r\nDECLARE\r\n   v_string      VARCHAR2 (1000) := 'Java|.Net|C|Oracle|PHP';\r\n\r\n   TYPE t_string IS TABLE OF VARCHAR2 (1000)\r\n                       INDEX BY BINARY_INTEGER;\r\n\r\n   t_str_array   t_string;\r\nBEGIN\r\n   FOR i IN 1 .. 5\r\n   LOOP\r\n      t_str_array (i) :=\r\n         getstring (v_string,\r\n                    i,\r\n                    TRUE,\r\n                    '|');\r\n   END LOOP;\r\n\r\n   FOR j IN t_str_array.FIRST .. t_str_array.LAST\r\n   LOOP\r\n      DBMS_OUTPUT.put_Line (t_str_array (j));\r\n   END LOOP;\r\nEND;\r\n\/<\/pre>\n
    Output:<\/h4>\n
    Java\r\n.Net\r\nC\r\nOracle\r\nPHP\r\nPL\/SQL procedure successfully completed.<\/pre>\n
    See also:<\/h4>\n