Add the split function to oracle

Source: Internet
Author: User

Add the split function in oracle. CREATE table TYPE www.2cto.com create or replace type "TYPE_SPLIT" as table of varchar2 (4000); 2. create or replace function split (p_list VARCHAR2, p_sep VARCHAR2: = ',') RETURN type_split pipelined is l_idx PLS_INTEGER; v_list VARCHAR2 (4000): = p_list; begin if p_list is null or p_list = ''then return; end if; LOOP l_idx: = instr (v_list, p_sep); IF l_idx> 0 then pipe row (substr (v _ List, 1, l_idx-1); v_list: = substr (v_list, l_idx + length (p_sep); else pipe row (v_list); EXIT; end if; end loop; RETURN; END split; 3. we often use in for queries such as www.2cto.com SELECT t. * FROM stu t WHERE t. useId IN ('12, 1000 '); however, when the number of queried results exceeds, an error is returned. So we can use the preceding split to query SELECT t. * FROM stu t, (SELECT column_value cv from table (CAST (split ('12, 66 ') AS type_split) a WHERE t. userId =. cv, the first is to split the string '12, 66 'into four rows, split ('12, 66') and then, is to convert the split value to the split type type_split we define; cast (split ('12, 66 ') as type_split) This step does not seem to be needed, in the table, convert to table. The default name of the split field is column_value;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.