Oracle Field split substitution is merged into one

Read the online many of the Oracle field split instance, but did not fully meet the requirements, perhaps I am not good enough to understand the level, if there is a great God know and willing to tell me, you can private messages to me, here sincere thanks!

1.

First build the table and insert the test data

drop table col_split;

CREATE TABLE Col_split

(

var_id VARCHAR2 (6),

Var_value VARCHAR2 (80)

);

1--insert into col_split values (' 101001 ', ' spt001,spt2,spt3,spt004,spt005 ');

2--insert into col_split values (' 101002 ', ' spt001,spt2 ');

3--insert into col_split values (' 101005 ', ' spt001 ');

Programme one:

Substr and instr

Substr Intercept String

Instr Find where a string appears

SELECT var_value,substr (Var_value,1,instr (Var_value, ', ')-1)

From Col_split;

description of the situation that appears The 3 data is null , because the ', ' is not found, so there is no value, similarly if the second paragraph of the value is intercepted, the first 2 data will appear Null, and if the length of the field is large, the number of splits is redundant and looks very complex.

Scenario Two:

The Regexp_substr function uses

SELECT regexp_substr (Var_value, ' [^,]+ ', 1,rownum)

From Col_split

CONNECT by ROWNUM <=

Length (var_value)-Length (REPLACE (Var_value, ', ', ')) + 1

This can be a good solution when there is only one piece of data in the table, but if multiple lines are the same as the scheme one will produce null, it would bebetter to have an idea and loop through It- scenario three.

Programme III:

Iterate through The value values using the Plsql loop and split

try to write one is the count number of direct loops, the other is to use a cursor to iterate over the result set, but encountered a problem when doing a regular modification and into the time,plsql is not allowed at this time to use the collection.

Example:

DECLARE

Var_values VARCHAR2 (80);

I number:=0;

TYPE filter is TABLE of VARCHAR2 (1000);

S_filter filter;

CURSOR Cursor_split is a SELECT var_value from Col_split;

BEGIN

OPEN Cursor_split;

LOOP

I: = i+1;

Dbms_output.put_line (var_values);

FETCH cursor_split into var_values;

SELECT regexp_substr (Var_values, ' [^,]+ ', 1,rownum) into S_filter

From Col_split

CONNECT by ROWNUM <=

Length (var_values)-Length (REPLACE (var_values, ', ', ')) + 1;

EXIT when Cursor_split%notfound;

END LOOP;

Dbms_output.put_line (i);

END;

finally think of using shell processing,the shell intercept field in the substitution is OK a direct fetch field to replace, the second kind is I want to say, Spool + sed

Spool the database fields in the format you want, and then generates the txt text. Then It's OK to replace the text content with sed . The field names of the character types in this table can be described. Looks more convenient.

Here's an example:

#!/bin/sh

Sqlplus-s user/[email protected] <<!

Set head off # output header field, default to on

the length of the set linesize 30000 # line

Set echo off # displays The sqlplus of each SQL command itself

Set Feedback off # echo this SQL command to process the number of rows recorded

Set pagesize 0 # output number of rows per page, set to 0 , in order to prevent paging

Set Termout off # shows the execution results of commands in the script

Set Trimout on # removes trailing spaces for each line of standard output, off by default

Set Trimspool on # de-redirect (spool) output trailing spaces per line default to off

Spool/home/expdata.txt output directed position, write file

SELECT * from Object.table_name where rownum=1;

Spool off

Exit

!

Sed ' s/ need to replace / Replace the value /g '; ' s/ need to replace / Replace the value /g ' expdata.txt

Ok this is OK to view the Expdata.txt text file.

Oracle Field split substitution is merged into one