--1. 字符串(分隔符分隔组成的字符串)转换成多行记录的表
-- Function
create FUNCTION [dbo].[fn_Split1](
@String nvarchar (max),
@Delimiter nvarchar (10)
)
RETURNS @ValueTable TABLE ([Value] NVARCHAR(max),[id] int)
BEGIN
DECLARE @NextString nvarchar(max),
@Pos int,
@NextPos int,
@CommaCheck nvarchar(1),
@id int
set @id=1
SET @NextString = ''
SET @CommaCheck = right(@String,1)
SET @String = @String + @Delimiter
SET @Pos = CHARINDEX(@Delimiter,@String)
SET @NextPos = 1
WHILE (@pos <> 0)
BEGIN
SET @NextString = SUBSTRING(@String,1,@Pos - 1)
INSERT INTO @ValueTable ( [Value],[id]) VALUES (@NextString,@id)
SET @String = SUBSTRING(@String,@pos +1,LEN(@String))
SET @NextPos = @Pos
SET @pos = CHARINDEX(@Delimiter,@String)
set @id = @id +1
END
RETURN
END
GO
--使用
SELECT * FROM dbo.fn_Split1('11111;22222;3333;4444',';');
--结果,列如下
/*
Value id
----------
11111 1
22222 2
3333 3
4444 4
*/
--2.多行记录某一个列,转换成一个字符串
IF EXISTS(SELECT TOP 1 1 FROM sysobjects WHERE id=OBJECT_ID('fn_GetProductTypeCodeList') AND type='FN')
DROP FUNCTION fn_GetProductTypeCodeList
GO
CREATE FUNCTION fn_GetProductTypeCodeList
( @chvReportProductTypeGUID varchar(40))
RETURNS varchar(max)
AS
BEGIN
declare @chvProductTypeCodeList varchar(max)
set @chvProductTypeCodeList = ''
select @chvProductTypeCodeList = @chvProductTypeCodeList+';'+ ProductTypeCode from p_Report2ProductType
WHERE ReportProductTypeGUID=@chvReportProductTypeGUID
set @chvProductTypeCodeList = stuff(@chvProductTypeCodeList,1,1,'')
return @chvProductTypeCodeList
END
GO
--使用
SELECT dbo.fn_GetProductTypeCodeList(NEWID())
--结果
/*
--多行数据
Value id
--------------
11111 1
22222 2
3333 3
4444 4
--最后得到
11111;22222;3333;4444
*/
sql 分隔符字符串转换成表以及多行记录列值转换成字符串
最新推荐文章于 2023-03-14 11:38:19 发布