SQL Server 2016 引入了一个新的内置表值函数STRING_SPLIT，它将指定的分隔字符拆分提供的输入字符串，并以 table 的形式返回输出分隔值，每个分隔符之间的每个分隔值都有一行。

STRING_SPLIT 函数有两个参数：

STRING_SPLIT （字符串，分隔符）

该字符串是具有 char，nchar，varchar 或 nvarchar 数据类型的字符表达式。分隔符是单个字符，用于将连接的输入字符串与 char，nchar，varchar 或 nvarchar 数据类型分开。
STRING_SPLIT 返回单个列表。返回列的名称是 value。值列的默认数据类型是 varchar。如果输入字符串数据类型为 nvarchar 或 nchar，则值列数据类型将为 nvarchar。值列的长度将与要拆分的字符串的长度相同。
在使用 STRING_SPLIT 函数之前，应确保数据库兼容级别为 130.如果不是，请修改

USE [ master ] GO 
ALTER DATABASE [ 数据库实例 ] SET COMPATIBILITY_LEVEL = 130 GO 
   
SELECT * FROM STRING_SPLIT （'John，Jeremy，Jack' ，'，' ）

那么，这么实用的功能在2008中怎么实用呢？首先贴出他的函数。函数名为split

USE [ceshi]
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 08/05/2019 13:20:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[Split]
(
  @Expression varchar(8000)
  , @Delimiter varchar(10)
)
RETURNS @Values table(Value varchar(2000))
AS
BEGIN
  declare @i int, @dl int, @l int, @value varchar(2000)
  select @i = charindex(@Delimiter, @Expression), @dl = len(@Delimiter), @l = len(@Expression)

  while (@i > )
  begin
    select @value = ltrim(rtrim(left(@Expression, @i - 1))), @Expression = substring(@Expression, @i + 1, @l)
    insert into @Values (value) values(@value)
    select @i = charindex(@Delimiter, @Expression), @l = len(@Expression)
  end

  set @Value = rtrim(ltrim(@Expression))
  if @Value <> '' insert into @Values (Value) values(@Value)  
  return
END

示例：

declare @value varchar(100)
set @value=('张三，李四，王五')

select * from dbo.split(@value,'，')

/*还可以带where条件*/

declare @value varchar(100)
set @value=('张三，李四，王五')
select * from dbo.split(@value,'，') where Value='张三'

end.

------------------------------2019-12-06-----------

最近几天在做一个接口，要求将两个列的结果，行转列。马上想到以上自定义函数split。做法是现将结果生成xml,xml里的内容用逗号分隔，在用split函数拆分。此间用到了cross APPLY .发现关联后的结果会出现重复记录，会出现重复记录，会出现重复记录。

又在网上找到另一位大神写的分割函数。感谢。

我用的是第一段。

CREATE FUNCTION [dbo].[SplitString]
(
    @str NVARCHAR(4000)
   ,@char NVARCHAR(10) = ','
)
RETURNS @SplitStr TABLE
(
     ID int IDENTITY PRIMARY KEY
    ,Value nvarchar(2000)
)
AS
    BEGIN
        SET @str = @str + @char
        WHILE LEN(@str) > 
            BEGIN
                INSERT @SplitStr
                SELECT  SUBSTRING(@str, 1, CHARINDEX(@char, @str) - 1)
                SELECT  @str = RIGHT(@str, LEN(@str) - CHARINDEX(@char, @str) - (LEN(@char) - 1))
            END   
        RETURN
    END

贴出其中一段我写的sql。

declare 
 @bdate datetime,
 @edate datetime

set  @bdate='2019-11-01 00:00:00'
set  @edate='2019-11-30 23:59:59'

select vac01  into #vac1 from VAC1  where VAC37 between @bdate and @edate and VAC45=1 


SELECT 
	a.vaa07
	,'' 医疗机构代码
    ,''机构名称
	,isnull(iak05,'') 医疗保险手册卡号
	,''健康卡号 
	,ACF01 就诊类型
	,vaa02 就诊卡号
	,VAA05 姓名
	,e.ABW01 性别
	,vaa12 出生日期
	,b.VAA10 年龄
	,case when len(VAA15)=18 then '01' else '' end 注册证件类型代码
	,CASE when len(VAA15)=18 then VAA15 else '' end 注册证件号码
	,CASE bck03 
	when '产科' then'0502'when '儿科' then'07'when '内科' then'03'when '外科' then'0401'when '中医科' then'50'
	when 'VIP门诊' then'02'when '生殖科' then'0505'when '五官科' then'02'when '妇科' then'0501' when '眼科' then'10'
	when '耳鼻咽喉科' then'11'when '口腔科' then'12'  when '皮肤科' then'13'when '预防保健科' then'0' else '02' end 就诊科室代码
	,vac71 就诊次数
	,VAC37 就诊日期
	,'无' 主诉
	,''重症监护室名称1
	,''进入时间1
	,''退出时间1
	,''重症监护室名称2
	,''进入时间2
	,''退出时间2
	,''重症监护室名称3
	,''进入时间3 
	,''退出时间3
	,''重症监护室名称4
	,''进入时间4
	,''退出时间4
	,''重症监护室名称5
	,''进入时间5
	,''退出时间5
	,case b.BDP02 when '医保' then'城镇职工基本医疗保险' when '自费' then '全自费' when '商保'then '商业医疗保险' end 医疗费用支付方式代码
   ,(select SUM(VAJ38) from #VAC1  a join vaj1 b on a.vac01=b.vaa07 where  VAJ05=2 ) 总费用
	,(select SUM(VAJ38) from #VAC1  a join vaj1 b on a.vac01=b.vaa07 where  VAJ05=2 and BDN01='R' ) 挂号费
	,(select SUM(VAJ38) from #VAC1  a join vaj1 b on a.vac01=b.vaa07 where  VAJ05=2 and BDN01 in ('1','2','3') )药品费
	,(select SUM(VAJ38) from #VAC1  a join vaj1 b on a.vac01=b.vaa07 where  VAJ05=2 and BDN01 in ('E','L') ) 检查费
	,(select SUM(VAJ38) from #VAC1  a join vaj1 b on a.vac01=b.vaa07 where  VAJ05=2 and VAJ39=1)自付费用
	,'2' 是否死亡 
	,'' 死亡时间 
	,vao15= STUFF((SELECT distinct ','+BAK02+','+b.VAO15 
					FROM vao1 b 
						left join BAK1 c on c.BAK01=b.BAK01A
						join #vac1 d on d.VAC01=b.VAA07		
					where  a.vaa07=b.VAA07   and bak02 like 'j%' --次要诊断
		FOR XML PATH('')),1,1,'') 
into #a1 
FROM VAO1  a  
			join VAC1 b on a.VAA07=b.VAC01
			JOIN BCB1 c On b.BCB01A = c.BCB01 
			JOIN BCK1 d on d.BCK01=b.BCK01A 
			join VAA1 e on e.VAA01=b.VAA01
			join AAU1 f on f.AAU01=e.AAU01
			join #vac1 g on g.VAC01=a.VAA07
			left join BAK1 g1 on g1.BAK01=a.BAK01A
WHERE    VAO06=1  and bak02 like 'j%' and a.ACF01=1
select  * into #bb1 from (

			/*去重复*/
			SELECT * FROM #a1 A
			 outer  APPLY [dbo].[SplitString](a.VAO15,',') AS b
			
) 
AS P1
PIVOT
(max(VALUE) FOR ID IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22])
)AS B
order by vaa07
select 
医疗机构代码 AS	P900
,机构名称 AS	P6891
,医疗保险手册卡号 AS	P686
,健康卡号 AS	P800
,就诊类型 AS	P7501
,就诊卡号 AS	P7502
,姓名 AS	P4
,性别 AS	P5
,出生日期	AS P6
,年龄	AS P7
,注册证件类型代码	AS P7503
,注册证件号码	AS P13
,就诊科室代码 AS	P7504
,就诊次数 AS	P7505
,就诊日期 AS	P7506
,主诉	P7507
,[1] AS	P321
,[2] AS 	P322
,[3] AS 	P324
,[4] AS 	P325
,[5] AS	P327
,[6] AS	P328
,[7] AS	P3291
,[8] AS	P3292
,[9] AS P3294
,[10] AS	P3295
,[11] AS P3297
,[12] AS P3298   --其他疾病诊断描述 5
,[13]  AS P3281
,[14] AS  P3282
,[15] AS P3284
,[16] AS P3285
,[17] AS P3287
,[18] AS P3288
,[19] AS P3271
,[20] AS P3272
,[21] AS P3274
,[22] AS P3275--其他疾病诊断描述 10
,重症监护室名称1	P6911
,进入时间1	P6912
,退出时间1	P6913
,重症监护室名称2	P6914
,进入时间2	P6915
,退出时间2	P6916
,重症监护室名称3	P6917
,进入时间3	P6918
,退出时间3	P6919
,重症监护室名称4	P6920
,进入时间4	P6921
,退出时间4	P6922
,重症监护室名称5	P6923
,进入时间5	P6924
,退出时间5	P6925
,医疗费用支付方式代码	P1
,总费用	P7508
,挂号费	P7509
,药品费	P7510
,检查费	P7511
,自付费用	P7512
,是否死亡	P8508
,死亡时间	P8509
from #bb1

drop table #a1
drop table #vac1
drop table #bb1

ITPub博客

SQL Server 2016 引入的内置表值函数STRING_SPLIT()在2008中的应用

最新文章