>

字段拆分替换在合并成一条,字符串和关系格式

- 编辑:www.bifa688.com -

字段拆分替换在合并成一条,字符串和关系格式

在数据库开辟进度中,字符串和关联表的倒车是一项基本才能。当字符串中留存分隔符时,有时将其转变来关系表数据,和任何数据表实行join查询,出现那种气象,是因为未有服从关周详据库的宏图范式,未有把字符串拆分成原子项存款和储蓄,也有相当大恐怕是数额传参数;有时会遇上相反的情状,须求将关乎表的连带数据拼接成3个字符串展现,或传参。

看了网络海人民广播广播台湾大学Oracle字段拆分的实例,可是都不可能完全满足供给,或者是自家水平不够未能很好的明亮,假诺有大神了解并且愿意告诉自身的,能够私信小编,在此间衷心的感恩戴义!

把格式化的字符串转化成关系格式,基本思路分为三种:

1.

  • 利用TSQL的循环语句:每便巡回都插入到事关表变量或一时表中,这种思路是面向进度的编制程序;
  • 使用XML查询:先把字符串转化成XML格式,再使用XML的nodes()函数,把XML数据转化成关周详据;那种思路是面向会集的编制程序,提出选取XML查询完结;

率先创立表并插入测试数据

把涉及格式转化成字符串,基本思路分为三种:

drop table col_88bifa必发唯一官网,split;

  • 利用TSQL的游标,对字符串施行累加连接,那种思路是面向过程的编制程序;
  • 动用XML查询的for xml path子句,把事关格式转化成字符串;那种思路是面向会集的编制程序,建议利用XML查询达成;

create table col_split

一,将字符串调换来表

(

先把字符串转变到XML格式,再利用XML的nodes()函数,把XML数据转化成关周密据,那种实现方式品质快,代码简洁,

var_id varchar2(6),

declare @separator varchar(10)
declare @str varchar(max)

set @separator=','
set @str='54,57,55,56,59'

var_value varchar2(80)

一,把字符串转化成节点值

);

每个子串都以节点值,只供给抽出节点值,即可把节点值转化成关系格式的列值

 

declare @xml xml
set @xml=convert(xml,'<v>'   REPLACE(@str, @separator, '</v><v>')   '</v>')

SELECT ids=N.v.value('.', 'int') 
FROM @xml.nodes('/v') N(v)

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);

declare @xml xml
set @xml=convert(xml,'<Item v='''   REPLACE(@str, @separator, '''></Item><Item v=''')   '''></Item>')

SELECT ids=N.v.value('@v', 'int') 
FROM @xml.nodes('/Item') N(v)

 

三,内置表值函数(string_split)

方案一:

SQL Server 201六新扩大二个表值函数string_split,用于遵照分隔符将字符串分割成表值数据,再次来到的字段名是Value

Substr和instr

STRING_SPLIT ( string , separator )  

Substr 截取字符串

2,将表数据拼接成字符串

Instr  查找字符串出现的任务标注

有以下数据表,有两列:ID和txt,ID值有再一次,而txt是文本数据;

 

create table dbo.test
(
ID int,
txt varchar(10)
)

SELECT var_value,SUBSTR(var_value,1,INSTR(var_value,',')-1)

把ID字段一样的txt字段的值拼接成字符串突显

FROM  col_split ;

select ID
    ,(select a.txt '' from dbo.test a where a.ID=t.ID for xml path('')) as descr
from dbo.test t 
group by ID

出现的情况评释 第贰条数据是null ,因为找不到‘,’号,所以未有值,同样如若截取第二段的值,第二条数据将晤面世null,而且只要字段长度大,分割的多那种写法就能大方的冗余,看起来11分的盘根错节。

3,奇巧淫技

 

在master数据库中,存在3个系列视图:master.dbo.spt_values,该视图包蕴从0到2047的具备数字,利用那特性格,能够把特定长度的字符串转化成关系格式,完成的代码如下:

方案二:

;with cte_numbers as 
(
    select number
    from master.dbo.spt_values
    where type='p'
        and number>0
)
select 
    cast(substring(@str, n.number, charindex(@separator ,@str  @separator ,n.number )-n.number)  as  nvarchar(4000)) as item 
    --,n.number
 from cte_numbers n
 where n.number<=len(@str) 1 
    and charindex(@separator,@separator @str,n.number)=n.number

REGEXP_SUBST君越 函数使用

对此该方法,要体会其代码的笔触,通过数据系列,从数字一始发,每一种检查实验分隔符,对字符串实行剪切操作,截取子字符串,从而把字符串转化成关系表;由于master.dbo.spt_values只有0到20肆捌个顺序数字,在须要时,能够替换该连串视图,而采用自定义的数据体系表,以增添能够拆分的字符串长度。

SELECT REGEXP_SUBSTR (var_value, '[^,] ', 1,ROWNUM)

在数据库开拓中,实现字符串和关联格式的并行转化,我同情于选拔面向集合的查询,通过面向进程的编制程序理念来促成,思路直接,比较轻便,在此就不再赘言了。

    FROM col_split

 

    CONNECT BY ROWNUM <=

参照文档:

LENGTH (var_value) - LENGTH (REPLACE (var_value, ',','')) 1

Why (and how) to split column using master..spt_values?

当表中只设有一条数据的时候这么些能够很好的消除,然而假诺多条跟方案壹一样会发出null,此时发生主张,循环遍历是还是不是会众多--方案3。

 

方案三:

  利用plsql 循环遍历value值,并实行划分

试着写壹种是count条数直接循环,另壹种是运用游标将结果集遍历出来,可是遭逢标题在做正则修改然后into的时候,plsql是不一样意在那一年利用收集的。

例子:

 

DECLARE

 

var_values VARCHAR2(80);

 

i NUMBER:=0;

 

TYPE filter IS TABLE OF VARCHAR2(1000);

 

s_filter filter;

 

CURSOR cursor_split IS 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;

 

最终想到利用shell管理,shell截取字段在做替换就ok1种直接取字段做替换,第3种是自家想说的,spool sed

Spool将数据库字段依据想要的格式拼接查询,然后生成txt文本。然后在动用sed替换文本内容就OK了。那样表中字符型的字段名称能够对应上表明。看起来更有益。

正如例子:

 

#!/bin/sh

 

sqlplus -s user/userpwd@sid << !

 

set head off  #输出标题域,缺省为on

 

set linesize 30000  #一行的长度

 

set echo off  #来得sqlplus的各类sql命令本身

 

set feedback off #回显本次sql命令管理记录的行数

 

set pagesize 0 #输出每页行数,二四 设定成0 ,为了堤防分页

 

set termout off  #彰显脚本中的命令的进行结果

 

set trimout on  #删去规范输出每行的拖尾空格,缺省为off

 

set trimspool on  #去除重定向(spool)输出每行的拖尾空格 缺省为off

 

spool /home/expdata.txt  输出定向的职责,写入文件

 

select * from object.table_name where rownum=1;

 

spool off

 

exit

 

!

 sed 's/必要替换掉的/替换的值/g'; 's/要求替换掉的/替换的值/g'  expdata.txt 

 

OK 这是查看expdata.txt 文本文件就OK了。

 

本文由88bifa必发唯一官网发布,转载请注明来源:字段拆分替换在合并成一条,字符串和关系格式