Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle creates split and splitstr functions     - Spring MVC Exception Handling (Programming)

- Linux device driver development small example --LED lights (Programming)

- Scope of variables in Object-C (Programming)

- You may not know the Linux command-line network monitoring tool (Linux)

- redis main building and disaster recovery from a cluster deployment (Database)

- Chromium Install Flash Official Guide (Linux)

- installation of Piwik under Ubuntu (Programming)

- MongoDB common optimization settings in Linux (Database)

- How to install Ubuntu strategy game Wesnoth 1.12.0 (Linux)

- Ubuntu in Vim editor display processing method Chinese garbled (Linux)

- Install Websphere MB required system rpm package under Linux (Linux)

- Oracle Database Performance Optimization of memory disk (Database)

- APR for Tomcat installation under Linux (Server)

- Oracle 10g in the unique and index problems (Database)

- Use NTFS-3G to mount NTFS partitions under Linux U disk and removable hard disk (Linux)

- Oracle first Automated Installation Packages (Database)

- Configuration based on open source Lucene Java development environment (Server)

- Linux ps command (Linux)

- Tomcat itself through simple movement separation (Server)

- Linux Detailed instructions alias settings (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:      
 
- Android recyclerview cardview (Programming)
- The oh-my-zsh into true my zsh (Linux)
- How to build a container cluster (Server)
- Python data types summary (Programming)
- Arrow keys, backspace key garbled in Python-2.7.5 Interactive Mode under CentOS 5.8 (Linux)
- Installation and configuration of Hadoop under Linux (Server)
- Ubuntu 14.10 installation SecureCRT 7.3 (Linux)
- AngularJS asynchronous service testing and Mocking (Programming)
- Installation of Ubuntu Make under Ubuntu 15.10 (Linux)
- Some problems and countermeasures Linux system calls exist (Linux)
- Ubuntu install Geary (Linux)
- Python function arguments * args and ** kwargs usage (Programming)
- A brief description of Java 8 new features introduced syntax (Programming)
- Google open source TCP team repaired the Linux kernel TCP flaw (Linux)
- Java Generics Introduction --HashMap summary (Programming)
- Linux file permissions to modify the command: chmod (Linux)
- To help you easily protect the Linux System (Linux)
- Linux scheduling summary (Linux)
- When RHEL7 use fdisk partition, all partitions can not be used (Linux)
- Bash variable expansion modifier (Programming)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.