Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle creates split and splitstr functions     - Zabbix installation and configuration process (Server)

- How comprehensive resist attacks from the network (Linux)

- Linux environment variable configuration and save places (Linux)

- Install Krita 2.8 on Ubuntu 13.10 / 12.04 / 12.10 (Linux)

- JIRA 3.6.2 Upgrade from older version to the new version 6.0.8 (Linux)

- CentOS environment prepared crontab scheduled tasks (Linux)

- Linux Learning Notes: Users and Groups (Linux)

- The difference between Objective-C language nil, Nil, NULL, NSNull (Programming)

- Caffe install under Ubuntu 14.04 64bit (Linux)

- To install the Git and Github under Ubuntu (Linux)

- Android View event delivery (Programming)

- Ubuntu compiler installation R Full Record (Linux)

- Shell scripts quickly deploy Tomcat project (Server)

- iptraf: A Practical TCP / UDP network monitoring tools (Linux)

- CentOS 5.10 installed Oracle 11G R2 (Database)

- Configuring automatic mail GAMIT under CentOS system (Linux)

- Oracle 11g em start newspaper site's security certificate has a solution to the problem (Database)

- Linux Operating System Security Study (Linux)

- Install Visual Studio Code in Ubuntu (Linux)

- Repair Raspbian Encountered a section with no Package (Linux)

 
         
  Oracle creates split and splitstr functions
     
  Add Date : 2018-11-21      
         
         
         
  The Sql statement is best executed in order

/ **************************************
 * Name: split
 * Function: Returns the type of the table after the character is divided by the specified character.
 * Parameters: p_list: string to be split.
                P_sep: delimiter, the default comma, can also specify a character or string.
 * Example: select * from users where u_id in (select column_value from table (split ( '1,2')))
                Returns two rows of data with u_id of 1 and 2.
 ************************************** /
/ * Create a table type * /
Create or replace type tabletype as table of varchar2 (32676)

/ * Create split function * /
Create or replace function split (p_list clob, p_sep varchar2: = ',')
  Return tabletype
  Pipelined

Is
  L_idx pls_integer;
  V_list varchar2 (32676): = p_list;
Begin
  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;
End;

 

/ **************************************
 * Name: splitstr
 * Author: sean zhang.
 * Date: 2012-09-03.
 * Function: Returns the string of the specified node after the specified character has been divided.
 * Parameters: str: string to be split.
                I: Returns the number of nodes. When i is 0 returns all the characters in str, when i exceeds the number can be split to return empty.
                Sep: delimiter, the default comma, can also specify a character or string. Returns the character in sep when the specified delimiter does not exist in str.
 * Example: select splitstr ( 'abc, def', 1) as str from dual;
                Select splitstr ( 'abc, def', 3) as str from dual;
 ************************************** /
/ * Create splitstr function * /
Create or replace function splitstr (str in clob,
                                    I in number: = 0,
                                    Sep in varchar2: = ',') return varchar2 is
  T_i number;
  T_count number;
  T_str varchar2 (4000);
Begin
  If i = 0 then
    T_str: = str;
  Elsif instr (str, sep) = 0 then
    T_str: = sep;
  Else
    Select count (*) into t_count from table (split (str, sep));
  
    If i < = t_count then
      Select str
        Into t_str
        From (select rownum as item, column_value as str
                From table (split (str, sep)))
      Where item = i;
    End if;
  End if;

  Return t_str;
End;

Example: split (string, identity)

 

Select split ( 'a, b, c, e, d, f, g') arrData from dual;

Default split with commas, you can customize the modification, such as: select split ( 'X-rapido & Lemon', '&') arrData from dual;

Default split with commas, you can customize the modification, such as: select split ( 'X-rapido & Lemon', '&') arrData from dual;

Example: splitstr (string, get the node subscript, delimiter)

Select splitstr ( 'X-rapido & Lemon & Jennifer', 1, '&') word from dual; - X-rapido
Select splitstr ( 'X-rapido & Lemon & Jennifer', 2, '&') word from dual;
Select splitstr ( 'X-rapido & Lemon & Jennifer', 3, '&') word from dual; - Jennifer
Select splitstr ( 'X-rapido & Lemon & Jennifer', 4, '&') word from dual; - empty string
     
         
         
         
  More:      
 
- Simple security measures to reinforce the Linux kernel (Linux)
- Ubuntu 12.04 installation DHCP Server (Server)
- MongoDB configuration in Ubuntu 14.04 (Database)
- ORA-08102 errors (Database)
- Use regular expressions to check whether the input box to enter a URL (Programming)
- To_explore Linux system boot process (Linux)
- installation of Vim plugin YouCompleteMe under Ubuntu 15.04 (Linux)
- Linux operating system security management skills notes (Linux)
- Open source backup software installed on Ubuntu Systemback 1.6.201 (Linux)
- Install minimize RHEL / CentOS 7 some things need to do (Linux)
- Forwarding module with Apache reverse proxy server (Server)
- Inherent limitations of Linux systems network security (Linux)
- Linux LVM space expansion Demo (Linux)
- Testing Oracle 11g RMAN replicate database (Database)
- Create Your Own Docker base image in two ways (Linux)
- Linux Defensive / mitigate DDOS attacks (Linux)
- Install Gnome Flashback Classic Desktop on Ubuntu 14.10 / Mint 7 (Linux)
- Ubuntu 14.10 / 14.04 how to install Quick Start tool Mutate 2.2 (Linux)
- Debian (Wheezy) Install Java environment / replace OpenJDK as the SUN JDK (Linux)
- MySQL query optimization: profile function (Database)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.