|
Posted on
2009-04-11 15:55
漂泊雪狼
阅读( 3751)
评论()
编辑
收藏
举报
SQL Server 2005 T-SQL 字符串拆分 split
一般的时候数据库设计中,有一对多或多对多的关系往往会设计一个表来存储这种关系的对应,但有时数据库结构我们是不能修改的,只能允许我们读取,现在假如有一下的数据表,需要我们来拆分字段char_array了。
有一段有趣的SQL 代码可以很好的解决这个问题:
创建辅助数据表 1IF OBJECT_ID('dbo.Nums') IS NOT NULL 2 DROP TABLE dbo.Nums; 3GO 4CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY); 5DECLARE @max AS INT, @rc AS INT; 6SET @max = 1000000; 7SET @rc = 1; 8 9INSERT INTO Nums VALUES(1); 10WHILE @rc * 2 <= @max 11BEGIN 12 INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums; 13 SET @rc = @rc * 2; 14END 15 16INSERT INTO dbo.Nums 17 SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max; 18GO
创建需要拆分的数据表 1IF OBJECT_ID('dbo.Arrays') IS NOT NULL 2 DROP TABLE dbo.Arrays; 3GO 4-- http://weiweictgu.cnblogs.com 5--come from sql server 2005 t-sql querying 6CREATE TABLE dbo.Arrays 7( 8 arrid VARCHAR(10) NOT NULL PRIMARY KEY, 9 array VARCHAR(8000) NOT NULL 10) 11 12INSERT INTO Arrays(arrid, array) VALUES('A', '20,22,25,25,14'); 13INSERT INTO Arrays(arrid, array) VALUES('B', '30,33,28'); 14INSERT INTO Arrays(arrid, array) VALUES('C', '12,10,8,12,12,13,12,14,10,9'); 15INSERT INTO Arrays(arrid, array) VALUES('D', '-4,-6,-4,-2'); 16GO
读取拆分符号','位置几个步骤 1-- Solution to Separating Elements Problem, Step 1 2SELECT arrid, array, n 3FROM dbo.Arrays 4 JOIN dbo.Nums 5 ON n <= LEN(array) 6 AND SUBSTRING(array, n, 1) = ','; 7 8-- Solution to Separating Elements Problem, Step 2 9SELECT arrid, array, n 10FROM dbo.Arrays 11 JOIN dbo.Nums 12 ON n <= LEN(array) 13 AND SUBSTRING(',' + array, n, 1) = ','; 14 15-- Solution to Separating Elements Problem, Step 3 16SELECT arrid, 17 SUBSTRING(array, n, CHARINDEX(',', array + ',', n) - n) AS element 18FROM dbo.Arrays 19 JOIN dbo.Nums 20 ON n <= LEN(array) 21 AND SUBSTRING(',' + array, n, 1) = ','; 22 23-- Solution to Separating Elements Problem, Step 4 24SELECT arrid, 25 n - LEN(REPLACE(LEFT(array, n), ',', '')) + 1 AS pos, 26 CAST(SUBSTRING(array, n, CHARINDEX(',', array + ',', n) - n) 27 AS INT) AS element 28FROM dbo.Arrays 29 JOIN dbo.Nums 30 ON n <= LEN(array) 31 AND SUBSTRING(',' + array, n, 1) = ',';
在SQL Server2005中没有必要再建立数字辅助表,可以使用递归CTE来拆分字段,但这种方法受限于
SQL Server2005的递归语法,在其他数据库中需要考虑其他的方式来实现。
SQL Server 2005递归实现方式 1-- Solution based on Recursive CTEs 2WITH SplitCTE AS 3( 4 SELECT arrid, 1 AS pos, 1 AS startpos, 5 CHARINDEX(',', array + ',') - 1 AS endpos 6 FROM dbo.Arrays 7 WHERE LEN(array) > 0 8 9 UNION ALL 10 11 SELECT Prv.arrid, Prv.pos + 1, Prv.endpos + 2, 12 CHARINDEX(',', Cur.array + ',', Prv.endpos + 2) - 1 13 FROM SplitCTE AS Prv 14 JOIN dbo.Arrays AS Cur 15 ON Cur.arrid = Prv.arrid 16 AND CHARINDEX(',', Cur.array + ',', Prv.endpos + 2) > 0 17) 18SELECT A.arrid, pos, 19 CAST(SUBSTRING(array, startpos, endpos-startpos+1) AS INT) AS element 20FROM dbo.Arrays AS A 21 JOIN SplitCTE AS S 22 ON S.arrid = A.arrid 23ORDER BY arrid, pos;
通过上面的演示代码你应该已经明白怎么将一个字段的字符如何拆分开来了。
注:此代码摘选自《SQL Server 2005 技术内幕T—SQL查询》一书的源码,此文是该书里面的一个小的应用实例。
|