SQL Server 2008 中的拆分函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14911167/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Split function in SQL Server 2008
提问by user2078458
I have Table1with columns like this:
我有这样Table1的列：
+--+------+
|ID|Name |
+--+------+
|1 |MSSQL |
+--+------+
|2 |MySQl |
+--+------+
|3 |Oracle|
+--+------+
In Table2, I have a column like
在Table2，我有一个像
+------------+
|Databasename|
+------------+
|1,3 |
+------------+
|2 |
+------------+
|1,2 |
+------------+
My output should be:
我的输出应该是：
+------------+
|Databasename|
+------------+
|MSSQL,Oracle|
+------------+
|MySQL |
+------------+
|MSSQL,MYSQL |
+------------+
How do I get this, I need query for this..
我怎么得到这个，我需要查询这个..
回答by Taryn
First, your best solution is to not store data in a comma-separated list in your database. You should consider fixing the table structure.
首先，最好的解决方案是不要将数据存储在数据库中的逗号分隔列表中。您应该考虑修复表结构。
If you cannot alter the table structure, then you will need to split the data in the list to rows to assign the correct name. Once the data is split then you can concatenate the data back into the list.
如果您无法更改表结构，则需要将列表中的数据拆分为行以分配正确的名称。拆分数据后，您可以将数据连接回列表。
There are many different splitfunction that you can find online but here is a version that I typically use:
split您可以在网上找到许多不同的功能，但这里是我通常使用的一个版本：
CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (items varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end;
To get your result, I would start by applying the splitfunction and a row_number()since I do not see a unique key associated with each row. If you have a unique key on each row then you will not need the row_number():
为了获得您的结果，我将首先应用split函数和 a，row_number()因为我没有看到与每一行关联的唯一键。如果您在每一行上都有一个唯一键，那么您将不需要row_number()：
;with cte as
(
select rn, name, id
from
(
select row_number() over(order by (select 1)) rn,
databasename
from table2
) t2
cross apply dbo.split(t2.databasename, ',') i
inner join table1 t1
on i.items = t1.id
)
select *
from cte
This query breaks your comma-separated list into the following:
此查询将您的逗号分隔列表分解为以下内容：
| RN | NAME | ID |
--------------------
| 1 | MSSQL | 1 |
| 1 | Oracle | 3 |
| 2 | MySQl | 2 |
| 3 | MSSQL | 1 |
| 3 | MySQl | 2 |
Once you have the data in multiple rows with the correct name, then you can use STUFF()and FOR XML PATHto concatenate it into the list. You full query would be similar to this:
一旦您在多行中使用正确的 数据name，您就可以使用STUFF()和FOR XML PATH将其连接到列表中。您的完整查询将类似于：
;with cte as
(
select rn, name, id
from
(
select row_number() over(order by (select 1)) rn,
databasename
from table2
) t2
cross apply dbo.split(t2.databasename, ',') i
inner join table1 t1
on i.items = t1.id
)
select
STUFF(
(SELECT ', ' + c2.name
FROM cte c2
where c1.rn = c2.rn
order by c2.id
FOR XML PATH (''))
, 1, 1, '') Databasename
from cte c1
group by c1.rn
order by c1.rn;
See SQL Fiddle with Demo.
The result of the full query is:
完整查询的结果是：
| DATABASENAME |
------------------
| MSSQL, Oracle |
| MySQl |
| MSSQL, MySQl |
回答by Mikael Eriksson
You are asking for a split function but you do not have to split your values to get the result you want.
您要求拆分函数，但不必拆分您的值即可获得所需的结果。
This query builds the comma separated names list in a correlated subquery using the for xmltrick to concatenate values. It uses liketo figure out what values to use from Table1for each row in Table2.
此查询使用for xml连接值的技巧在相关子查询中构建逗号分隔的名称列表。它采用like弄清楚什么值从使用Table1中的每一行Table2。
select (
select ', '+T1.Name
from Table1 as T1
where ','+T2.Databasename+',' like '%,'+cast(T1.ID as varchar(10))+',%'
for xml path(''), type
).value('substring(text()[1], 3)', 'varchar(max)') as Databasenames
from Table2 as T2
回答by muhmud
No splitting, also no XML Path, but achieves the right result.
没有拆分，也没有 XML 路径，但达到了正确的结果。
;with cte as (
select *, cast(null as varchar(1024)) as str, cast(0 as int) as ID
from Table2
union all
select DatabaseName, (case when DatabaseName like cast(t.ID as varchar(32)) + ',%'
or DatabaseName like '%,' + cast(t.ID as varchar(32)) + ',%'
or DatabaseName like '%,' + cast(t.ID as varchar(32))
or DatabaseName = cast(t.ID as varchar(32)) then cast(isnull(str, '') + ',' + t.Name as varchar(1024)) else str end), cte.ID + 1 as ID
from cte
inner join Table1 t on cte.ID + 1 = t.ID
)
select DatabaseName, (case when str like ',%' then substring(str, 2, len(str)) else null end) as str
from cte c
where ID = (select max(ID) from cte where DatabaseName = c.DatabaseName)
回答by user3415785
--Here it goes:
----------------
-- FieldCount --
----------------
CREATE FUNCTION [dbo].[FieldCount](@S VARCHAR(8000), @Separator VARCHAR(10))
RETURNS INT
AS
BEGIN
/*
@Author: Leonardo Augusto Rezende Santos
@Contact: http://www.linkedin.com/pub/leonardo-santos/0/2b1/890
*/
DECLARE @Ptr INT, @p INT, @LenS INT, @LenSep INT, @Result INT
IF @Separator = ' '
BEGIN
SET @S = REPLACE(@S, ' ', '|-|')
SET @Separator = '|-|'
END
WHILE CHARINDEX(@Separator + @Separator, @S) > 0
SET @S = Replace(@S, @Separator + @Separator, @Separator + '_-_' + @Separator)
IF @S <> ''
SET @Result = 1
ELSE
BEGIN
SET @Result = 0
RETURN(@Result)
END
SET @Ptr = 0
SET @LenS = LEN(@S)
SET @LenSep = LEN(@Separator)
SET @p = CHARINDEX(@Separator, @S)
WHILE @p > 0
BEGIN
SET @Result = @Result + 1
SET @Ptr = @Ptr + @p + @LenSep
SET @p = CHARINDEX(@Separator, SUBSTRING(@S, @Ptr, @LenS - @Ptr + 1))
END
RETURN(@Result)
END
--------------
-- GetField --
--------------
CREATE FUNCTION [dbo].[GetField](@S VARCHAR(8000), @Separator VARCHAR(10), @Field INT)
RETURNS VARCHAR(8000)
AS
BEGIN
/*
@Author: Leonardo Augusto Rezende Santos
@Contact: http://www.linkedin.com/pub/leonardo-santos/0/2b1/890
*/
DECLARE @Ptr INT, @p INT, @LenS INT, @LenSep INT, @Fld INT, @Result VARCHAR(8000)
IF @Separator = ' '
BEGIN
SET @S = REPLACE(@S, ' ', '|-|')
SET @Separator = '|-|'
END
IF @Field > dbo.FieldCount(@S, @Separator)
BEGIN
SET @Result = ''
RETURN(@Result)
END
SET @Fld = 1
SET @Ptr = 1
SET @LenS = LEN(@S)
SET @LenSep = LEN(@Separator)
SET @p = CHARINDEX(@Separator, @S)
WHILE (@p > 0) and (@Fld < @Field)
BEGIN
SET @Fld = @Fld + 1
SET @Ptr = @Ptr + @p + @LenSep - 1
SET @p = CHARINDEX(@Separator, SUBSTRING(@S, @Ptr, @LenS - @Ptr + 1))
END
IF (@p = 0) and (@Fld = @Field)
SET @p = @LenS - @Ptr + 2
SET @Result = SUBSTRING(@S, @Ptr, @p - 1)
RETURN(@Result)
END
/* USAGE*/
select dbo.FieldCount('A1 A2 A3 A4 A5', ' ')
--It will return 5
select dbo.GetField('A1 A2 A3 A4 A5', ' ', 3)
--It will return 'A3'
select dbo.GetField('A1/A2/A3/A4/A5', '/', 3)
--It will return 'A3'
--Hope it works for you.
--Leonardo Augusto
