SQL Server中对字符串拆分与合并的语句指的是什么

发布时间：2023-05-08 14:13:19 所属栏目：MsSql教程来源：

导读：这篇我们来学习和了解“SQL Server中对字符串拆分与合并的语句是什么”，下文的讲解详细，步骤过程清晰，对大家进一步学习和理解“SQL Server中对字符串拆分与合并的语句是什么”有一定的帮助。

这篇我们来学习和了解“SQL Server中对字符串拆分与合并的语句是什么”，下文的讲解详细，步骤过程清晰，对大家进一步学习和理解“SQL Server中对字符串拆分与合并的语句是什么”有一定的帮助。有这方面学习需要的朋友就继续往下看吧！

概述

STRING_AGG（合并）：多行数据合并成一个字符串，以逗号隔开。

STRING_SPLIT（拆分）:一个字符串，拆分成多行。

一、多行数据合并成一个字符串

1、通过 FOR xml path('') 合并字符串记录

根据name字段，合并code

declare @table1 table (    id int ,code varchar(10) , name varchar(20) );

insert into @table1 ( id,code, name ) values ( 1, 'm1','a' ), ( 2, 'm2',null ), ( 3, 'm3', 'c' ), ( 4, 'm2','d' ), ( 5, 'm1','c' );

select * from @table1;

select name, files=stuff((select ','+convert(varchar, code)

                          from @table1 b

                          where a.name=b.name

                         for xml path('')), 1, 1, '')

from @table1 a

group by name;

2、MS SQL Server的2017新增了STRING_AGG()是一个聚合函数

它将由指定的分隔符分隔将字符串行连接成一个字符串。它不会在结果字符串的末尾添加分隔符。

SELECT name,   string_agg(code,';') files FROM @table1 GROUP BY name;

二、一个字符串拆分成多行

1、拆一列数据：

将如下从Excel复制的一栏数据，插入到表中行进显示（同时去掉回车换行符，空白和Tab符号）：

1、利用XML解析方式(推荐)

declare @moulds varchar(4000);

set @moulds='55-480730-03,

55-487780-01,

,

55-487780-02 ';

declare @table1 table(col1 nvarchar(4000));

declare @table2 table(col1 nvarchar(40),xmlval1 xml);

insert into @table1 values(replace(@moulds, char(13)+char(10), ''));

select * from @table1

insert into @table2

select   rtrim(ltrim(replace(bs.v1, char(9), '') )),a.xmlval1

from (select convert(xml, '<n>'+replace(replace(col1, '，', ','), ',', '</n><n>')+'</n>') as xmlval1

       from @table1) a

       cross apply(select k.n.value('.', 'nvarchar(80)') v1 from a.xmlval1.nodes('n') k(n) ) bs

where bs.v1 !='';

select * from @table2;

2、利用字符串拆解

declare @moulds varchar(4000);

set @moulds='55-480730-03,

55-487780-01,

,

55-487780-02 ';

declare @table1 table(col1 nvarchar(4000));

declare @table2 table(col1 nvarchar(40), pos int);

insert into @table1 values(replace(@moulds, char(13)+char(10), ''));

select * from @table1;

insert into @table2

select rtrim(ltrim(replace(substring(A.col1, B.number, charindex(',', A.col1+',', B.number)-B.number) , char(9), '') )) as col2, B.number

from @table1 A

     inner join master..spt_values B

         on charindex(',', ','+A.col1, B.number)=B.number

where B.type='P';

select * from @table2;

2、拆多列数据：

有如下数据表

需求就是将Col1，Col2按照特定的字符串分割成多行

先将该字段值统一替换为逗号分割，再将逗号分割替换转为XML数据类型，再利用xml转为多个行

declare @table1 table

    (

        ID int ,

        Col1 nvarchar(50) ,

        Col2 nvarchar(50)

    );

insert into @table1 values ( 1, 'a,b,c', '诶，必，塞，地，伊' );

insert into @table1 values ( 2, 'w', N'三四，不知道咧' );

--方式一

select a.ID, a.Col1, a.Col2, v1, v2

from   (   select ID, Col1, Col2, convert(xml, '<n>' + replace(replace(Col1, '，', ','), ',', '</n><n>') + '</n>') as xmlval1 ,

                                  convert(xml, '<n>' + replace(replace(Col2, '，', ','), ',', '</n><n>') + '</n>') as xmlval2

           from   @table1 ) a

       cross apply (   select k.n.value('.', 'nvarchar(80)') v1

                       from   a.xmlval1.nodes('n') k(n) ) bs

       cross apply (   select k.n.value('.', 'nvarchar(80)') v2

                       from   a.xmlval2.nodes('n') k(n) ) ns;

--方式二

select ID, t.Col1,t.Col2, v1, v2

from   @table1 as t

       cross apply ( values (convert(xml, '<n>' + replace(replace(Col1, '，', ','), ',', '</n><n>')+ '</n>'),

                             convert(xml, '<n>' + replace(replace(Col2, '，', ','), ',', '</n><n>')+ '</n>'))

                   ) a (xmlval1 , xmlval2 )

       cross apply (   select k.n.value('.', 'varchar(80)') as v1

                       from   a.xmlval1.nodes('n') k(n)) bs

       cross apply (   select k.n.value('.', 'varchar(80)') as v2

                       from   a.xmlval2.nodes('n') k(n) ) ns;

3、创建自定义拆分函数

函数功能：切分字符串，返回一个列名为id的表

--1. 创建fn_Split函数

IF EXISTS(

       SELECT *

       FROM   dbo.sysobjects

       WHERE id = OBJECT_ID('fn_Split')

              AND (TYPE = 'FN' OR TYPE = 'TF' OR TYPE = 'IF')

   )

    DROP FUNCTION fn_Split

GO

CREATE FUNCTION [dbo].[fn_Split]

(

    @str           VARCHAR(MAX),

    @separator     VARCHAR(10)

)

RETURNS TABLE

AS

    RETURN

    (

        SELECT B.id

        FROM   (

                   (

                          --A　的作用只是生成 '<v>a</v><v>b</v><v>d</v><v>c</v>' 的XML格式的数据, 提供数据源

                       SELECT [value] = CONVERT(XML, '<v>' + REPLACE(@str, @separator, '</v><v>') + '</v>')

                   ) A

                   OUTER APPLY

                   (

                          --B 的作用是将A中的 XML 数据的值枚举出来转换成行

                       SELECT id = N.v.value('.', 'varchar(100)') FROM   A.[value].nodes('/v') N(v)

                   ) B

               )

    )

GO

使用函数 SELECT id FROM fn_Split('a,b,d,c',',')

declare @moulds varchar(4000);

set @moulds='55-480730-03,

55-487780-01,

,

55-487780-02 ';

declare @table1 table(id INT,col1 nvarchar(MAX));

INSERT INTO @table1 VALUES(1,replace(@moulds, char(13)+char(10), ''))

INSERT INTO @table1 VALUES(2,replace(@moulds, char(13)+char(10), ''))

select * from @table1;

SELECT a.id,rtrim(ltrim(replace(b.id, char(10), '') )) AS item

FROM @table1 a CROSS APPLY dbo.fn_Split(a.col1,',') AS b

where b.id !=''

4、SQL Server 2016新增了string_split函数

专门用来拆分字符串。

SELECT t.id,

       t.name,

       t.description,

       v.value

FROM test t

    CROSS APPLY STRING_SPLIT(t.description, ',')v;

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!