我有一个这样的SQL表:
| SomeID | OtherID | Data
+----------------+-------------+-------------------
| abcdef-..... | cdef123-... | 18,20,22
| abcdef-..... | 4554a24-... | 17,19
| 987654-..... | 12324a2-... | 13,19,20
是否有一个查询，我可以执行查询，如SELECT OtherID, SplitData where SomeID = 'abcdef-.......，返回单独的行，就像这样:
| OtherID | SplitData
+-------------+-------------------
| cdef123-... | 18
| cdef123-... | 20
| cdef123-... | 22
| 4554a24-... | 17
| 4554a24-... | 19
基本上把我的数据在逗号处分割成单独的行?
我知道将逗号分隔的字符串存储到关系数据库中听起来很愚蠢，但消费者应用程序中的正常用例使这种方法非常有用。
我不想在应用程序中进行拆分，因为我需要分页，所以我想在重构整个应用程序之前探索选项。
它是SQL Server 2008(非r2)。
函数
CREATE FUNCTION dbo.SplitToRows (@column varchar(100), @separator varchar(10))
RETURNS @rtnTable TABLE
(
ID int identity(1,1),
ColumnA varchar(max)
)
AS
BEGIN
DECLARE @position int = 0;
DECLARE @endAt int = 0;
DECLARE @tempString varchar(100);
set @column = ltrim(rtrim(@column));
WHILE @position<=len(@column)
BEGIN
set @endAt = CHARINDEX(@separator,@column,@position);
if(@endAt=0)
begin
Insert into @rtnTable(ColumnA) Select substring(@column,@position,len(@column)-@position);
break;
end;
set @tempString = substring(ltrim(rtrim(@column)),@position,@endAt-@position);
Insert into @rtnTable(ColumnA) select @tempString;
set @position=@endAt+1;
END;
return;
END;
用例
select * from dbo.SplitToRows('T14; p226.0001; eee; 3554;', ';');
或者只是一个有多个结果集的选择
DECLARE @column varchar(max)= '1234; 4748;abcde; 324432';
DECLARE @separator varchar(10) = ';';
DECLARE @position int = 0;
DECLARE @endAt int = 0;
DECLARE @tempString varchar(100);
set @column = ltrim(rtrim(@column));
WHILE @position<=len(@column)
BEGIN
set @endAt = CHARINDEX(@separator,@column,@position);
if(@endAt=0)
begin
Select substring(@column,@position,len(@column)-@position);
break;
end;
set @tempString = substring(ltrim(rtrim(@column)),@position,@endAt-@position);
select @tempString;
set @position=@endAt+1;
END;
当使用这种方法时，您必须确保您的值中没有包含非法XML - user1151923
我总是使用XML方法。确保使用VALID XML。我有两个函数在有效的XML和文本之间转换。(我倾向于去掉回车，因为我通常不需要它们。
CREATE FUNCTION dbo.udf_ConvertTextToXML (@Text varchar(MAX))
RETURNS varchar(MAX)
AS
BEGIN
SET @Text = REPLACE(@Text,CHAR(10),'');
SET @Text = REPLACE(@Text,CHAR(13),'');
SET @Text = REPLACE(@Text,'<','<');
SET @Text = REPLACE(@Text,'&','&');
SET @Text = REPLACE(@Text,'>','>');
SET @Text = REPLACE(@Text,'''',''');
SET @Text = REPLACE(@Text,'"','"');
RETURN @Text;
END;
CREATE FUNCTION dbo.udf_ConvertTextFromXML (@Text VARCHAR(MAX))
RETURNS VARCHAR(max)
AS
BEGIN
SET @Text = REPLACE(@Text,'<','<');
SET @Text = REPLACE(@Text,'&','&');
SET @Text = REPLACE(@Text,'>','>');
SET @Text = REPLACE(@Text,''','''');
SET @Text = REPLACE(@Text,'"','"');
RETURN @Text;
END;
DECLARE @id_list VARCHAR(MAX) = '1234,23,56,576,1231,567,122,87876,57553,1216';
DECLARE @table TABLE ( id VARCHAR(50) );
DECLARE @x INT = 0;
DECLARE @firstcomma INT = 0;
DECLARE @nextcomma INT = 0;
SET @x = LEN(@id_list) - LEN(REPLACE(@id_list, ',', '')) + 1; -- number of ids in id_list
WHILE @x > 0
BEGIN
SET @nextcomma = CASE WHEN CHARINDEX(',', @id_list, @firstcomma + 1) = 0
THEN LEN(@id_list) + 1
ELSE CHARINDEX(',', @id_list, @firstcomma + 1)
END;
INSERT INTO @table
VALUES ( SUBSTRING(@id_list, @firstcomma + 1, (@nextcomma - @firstcomma) - 1) );
SET @firstcomma = CHARINDEX(',', @id_list, @firstcomma + 1);
SET @x = @x - 1;
END;
SELECT *
FROM @table;
函数
CREATE FUNCTION dbo.SplitToRows (@column varchar(100), @separator varchar(10))
RETURNS @rtnTable TABLE
(
ID int identity(1,1),
ColumnA varchar(max)
)
AS
BEGIN
DECLARE @position int = 0;
DECLARE @endAt int = 0;
DECLARE @tempString varchar(100);
set @column = ltrim(rtrim(@column));
WHILE @position<=len(@column)
BEGIN
set @endAt = CHARINDEX(@separator,@column,@position);
if(@endAt=0)
begin
Insert into @rtnTable(ColumnA) Select substring(@column,@position,len(@column)-@position);
break;
end;
set @tempString = substring(ltrim(rtrim(@column)),@position,@endAt-@position);
Insert into @rtnTable(ColumnA) select @tempString;
set @position=@endAt+1;
END;
return;
END;
用例
select * from dbo.SplitToRows('T14; p226.0001; eee; 3554;', ';');
或者只是一个有多个结果集的选择
DECLARE @column varchar(max)= '1234; 4748;abcde; 324432';
DECLARE @separator varchar(10) = ';';
DECLARE @position int = 0;
DECLARE @endAt int = 0;
DECLARE @tempString varchar(100);
set @column = ltrim(rtrim(@column));
WHILE @position<=len(@column)
BEGIN
set @endAt = CHARINDEX(@separator,@column,@position);
if(@endAt=0)
begin
Select substring(@column,@position,len(@column)-@position);
break;
end;
set @tempString = substring(ltrim(rtrim(@column)),@position,@endAt-@position);
select @tempString;
set @position=@endAt+1;
END;
截至2016年2月-见talltytable示例-很可能从2014年2月开始超过我的TVF。为子孙后代保留以下原始帖子:
上面的例子中重复的代码太多了。我不喜欢cte和XML的性能。还有一个显式Id，以便特定于订单的消费者可以指定order BY子句。
CREATE FUNCTION dbo.Split
(
@Line nvarchar(MAX),
@SplitOn nvarchar(5) = ','
)
RETURNS @RtnValue table
(
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Data nvarchar(100) NOT NULL
)
AS
BEGIN
IF @Line IS NULL RETURN;
DECLARE @split_on_len INT = LEN(@SplitOn);
DECLARE @start_at INT = 1;
DECLARE @end_at INT;
DECLARE @data_len INT;
WHILE 1=1
BEGIN
SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at);
SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @end_at-@start_at END;
INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len) );
IF @end_at = 0 BREAK;
SET @start_at = @end_at + @split_on_len;
END;
RETURN;
END;
