我有一个这样的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;
很晚了,但是试试这个:
SELECT ColumnID, Column1, value --Do not change 'value' name. Leave it as it is.
FROM tbl_Sample
CROSS APPLY STRING_SPLIT(Tags, ','); --'Tags' is the name of column containing comma separated values
所以我们有了这个:
tbl_Sample:
ColumnID| Column1 | Tags
--------|-----------|-------------
1 | ABC | 10,11,12
2 | PQR | 20,21,22
运行此查询后:
ColumnID| Column1 | value
--------|-----------|-----------
1 | ABC | 10
1 | ABC | 11
1 | ABC | 12
2 | PQR | 20
2 | PQR | 21
2 | PQR | 22
谢谢!
以下工作在sql server 2008
select *, ROW_NUMBER() OVER(order by items) as row#
from
( select 134 myColumn1, 34 myColumn2, 'd,c,k,e,f,g,h,a' comaSeperatedColumn) myTable
cross apply
SPLIT (rtrim(comaSeperatedColumn), ',') splitedTable -- gives 'items' column
将得到所有的笛卡尔积与原表列加上“项目”的分割表。
可以使用以下函数提取数据
CREATE FUNCTION [dbo].[SplitString]
(
@RowData NVARCHAR(MAX),
@Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE
(
ID INT IDENTITY(1,1),
Data NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Iterator INT;
SET @Iterator = 1;
DECLARE @FoundIndex INT;
SET @FoundIndex = CHARINDEX(@Delimeter,@RowData);
WHILE (@FoundIndex>0)
BEGIN
INSERT INTO @RtnValue (data)
SELECT
Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)));
SET @RowData = SUBSTRING(@RowData,
@FoundIndex + DATALENGTH(@Delimeter) / 2,
LEN(@RowData));
SET @Iterator = @Iterator + 1;
SET @FoundIndex = CHARINDEX(@Delimeter, @RowData);
END;
INSERT INTO @RtnValue (Data)
SELECT Data = LTRIM(RTRIM(@RowData));
RETURN;
END;