--
建立辅助表
SET NOCOUNT ON ;
USE AdventureWorks;
GO
IF OBJECT_ID ( ' dbo.Nums ' ) IS NOT NULL
DROP TABLE dbo.Nums;
GO
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 ;
-- 示例表
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 ' );
-- 实施方法
SELECT arrid, substring (array,n, charindex ( ' , ' ,array + ' , ' ,n) - n), n
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN (array)
AND SUBSTRING ( ' , ' + array, n, 1 ) = ' , '
/**/ /*
select arrid,charindex(',',array+',',n)-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) = ','
*/
SET NOCOUNT ON ;
USE AdventureWorks;
GO
IF OBJECT_ID ( ' dbo.Nums ' ) IS NOT NULL
DROP TABLE dbo.Nums;
GO
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 ;
-- 示例表
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 ' );
-- 实施方法
SELECT arrid, substring (array,n, charindex ( ' , ' ,array + ' , ' ,n) - n), n
FROM dbo.Arrays
JOIN dbo.Nums
ON n <= LEN (array)
AND SUBSTRING ( ' , ' + array, n, 1 ) = ' , '
/**/ /*
select arrid,charindex(',',array+',',n)-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) = ','
*/