SQL 字符串拼接与拆分

使用 SQL的 for xml path来进行字符串拼接

　　本篇主要讲怎么利用SQL的FORXMLPATH 参数来进行字符串拼接，FORXMLPATH的用法很简单，它会以xml文件的形式来返回数据。

　　小小示例一：

Create table test (id int,变量 varchar(10))
go
insert test values(1,'ac')
insert test values(1,'bs')
insert test values(1,'cg')
insert test values(1,'as')
insert test values(2,'ds')
insert test values(2,'dh')

go

select ID,LEFT(变量列表,LEN(变量列表)-1) as a
from 
(
 select ID,(select 变量+'|' from  test as B where B.id = A.id for XML path('')) as 变量列表
 from test as A
 group by ID
) as C

 

 

我的讲解步骤：

1：构造初始数据2：提出问题3：简单介绍FORXMLPATH
4：解答问题


1.构造初始数据 

举出一个经典的学生课程例子，共有学生、课程与学生课程三张表。


　　表1:Student


student_idstudent_name1
张三
2
李四
3王五




表2:Course

course_idcourse_name1
语言
2
数学
3英语




表3:Student_Course



student_idcourse_id
1
2
1
3
21
23
3
3




脚本：


createtable student
(
student_id intprimarykey,
student_name nvarchar(50) notnull
)

createtable course
(
course_id intprimarykey,
course_name nvarchar(50) notnull
)

createtable student_course
(
student_id intnotnull,
course_id intnotnull,
primarykey(student_id,course_id)
)





2.提出问题

写一条SQL语句，查询显示出下列结果：



student_namecourse_name
张三
数学，英语
李四
语言，英语
王五
英语





3.简单介绍 FORXMLPATH


FORXMLPATH 语句能够把查询的数据生成XML数据，举个例子，针对student表，以前SQL语句的查询结果为：




selectstr(student_id) +','+ student_name from student for xml path('student')
查询结果：


<student> 1,张三</student>
<student> 2,李四</student>
<student> 3,王五</student>



student已成为一个xml文件中的结点了，再看看FORXMLPATH('')的效果，针对上述SQL作出修改，


selectstr(student_id) +','+ student_name from student for xml path('')
查询结果：



1,张三 2,李四 3,王五



看得出来，这个参数自动把我们的查询结果串接在一起了，这下子，要做字符串拼接就很简单了！




4. 解答问题要查询想要的结果，我们首先用一般的SQL语句，连接三个表之后的结果为：



select a.student_name,b.course_name from student_course c,student a,course b where
c.student_id=a.student_id and c.course_id=b.course_id



查询结果：


student_namecourse_name
张三
数学
张三
英语
李四
语文
李四
英语
王五
英语



我们把这个查询结果看作为一个临时表，与自身进行一次连接，再得用FORXMLPATH('')参数来对课程course_name列进行拼接，再得用子查询功能。这样就得到一个每一个学生的所选的所有课程，由于上表会存在同一学生的多条记录，所以需要对最后的结果按学生进行分组，先看看查询语句：





select student_name,
(select course_name+','from
(
select student_name,course_name from
(
select a.student_name,b.course_name from stud_course c,student a,course b where c.student_id=a.student_id and c.course_id=b.course_id
) as a
) as b where c.student_name=b.student_name for xml path('')
) as course_name
from
(
select a.student_name,b.course_name from student_course c,student a,course b where c.student_id=a.student_id and c.course_id=b.course_id
) as c group by student_name




查询结果：



student_namecourse_name
张三
数学，英语,
李四
语言，英语,
王五
英语,




还有个小问题， course_name后面多出一个,号，最后做一次裁剪，假设上面的SQL语句作为一个子查询 subquery



select student_name,left(course_name,len(course_name)-1) from (........) as subquery

这样，就可以得出最终的结果！可以看得出来FORXMLPATH('') 参数非常强大！





PS:有很多人把这个叫行转列，我个人并不这么认为，虽然这和行转列有点像，但是这更像是字符串拼接！就把它这么叫好了!


PS:我的测试环境：SQL server 2008


























Author：repository
From： http://repository.cnblogs.com
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。     SQL 拆分字符串 
事例数据库1

USE tempdb;
GO
IF OBJECT_ID('dbo.Arrays') IS NOT NULL
DROP TABLE dbo.Arrays;
GO

CREATE TABLE dbo.Arrays
(
arrid VARCHAR(10) NOT NULL PRIMARY KEY,
array VARCHAR(8000) NOT NULL
)

INSERT INTO Arrays(arrid, array) VALUES('A', '20,22,25,25,14');
INSERT INTO Arrays(arrid, array) VALUES('B', '30,33,28');
INSERT INTO Arrays(arrid, array) VALUES('C', '12,10,8,12,12,13,12,14,10,9');
INSERT INTO Arrays(arrid, array) VALUES('D', '-4,-6,-4,-2');


事例数据库2


CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;

INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
GO



第一部思路 :
SELECT arrid, array, n
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN(array)
AND SUBSTRING(array, n, 1) = ',';

第二部思路 :

SELECT arrid, array, n
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN(array)
AND SUBSTRING(',' + array, n, 1) = ',';


第三部思路 : 


SELECT arrid,
SUBSTRING(array, n, CHARINDEX(',', array + ',', n) - n) AS element
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN(array)
AND SUBSTRING(',' + array, n, 1) = ',';


第四部思路 :
SELECT arrid,
n - LEN(REPLACE(LEFT(array, n), ',', '')) + 1 AS pos,
CAST(SUBSTRING(array, n, CHARINDEX(',', array + ',', n) - n)
AS INT) AS element
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN(array)
AND SUBSTRING(',' + array, n, 1) = ',';


SQL 2005 以上版本可使用,不使用Number辅助表
WITH SplitCTE AS
(
SELECT arrid, 1 AS pos, 1 AS startpos,
CHARINDEX(',', array + ',') - 1 AS endpos
FROM dbo.Arrays
WHERE LEN(array) > 0

UNION ALL

SELECT Prv.arrid, Prv.pos + 1, Prv.endpos + 2,
CHARINDEX(',', Cur.array + ',', Prv.endpos + 2) - 1
FROM SplitCTE AS Prv
JOIN dbo.Arrays AS Cur
ON Cur.arrid = Prv.arrid
AND CHARINDEX(',', Cur.array + ',', Prv.endpos + 2) > 0
)
SELECT A.arrid, pos,
CAST(SUBSTRING(array, startpos, endpos-startpos+1) AS INT) AS element
FROM dbo.Arrays AS A
JOIN SplitCTE AS S
ON S.arrid = A.arrid
ORDER BY arrid, pos;