SQL Server 字段拆分的小技巧

一般的时候数据库设计中，有一对多或多对多的关系往往会设计一个表来存储这种关系的对应，但有时数据库结构我们是不能修改的，只能允许我们读取，现在假如有一下的数据表，需要我们来拆分字段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的递归语法，在其他数据库中需要考虑其他的方式来实现。

 

 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查询》一书的源码，此文是该书里面的一个小的应用实例。