sql server 2005中分隔列中数据
<div style="padding-right: 5.4pt; padding-left: 5.4pt; background: #e6e6e6; padding-bottom: 4px; width: 95%; padding-top: 4px;">http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif--建立辅助表http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifSET NOCOUNT ON;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifUSE AdventureWorks;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifGO
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifIF OBJECT_ID('dbo.Nums') IS NOT NULL
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif DROP TABLE dbo.Nums;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifGO
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifCREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifDECLARE @max AS INT, @rc AS INT;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifSET @max = 1000000;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifSET @rc = 1;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifINSERT INTO Nums VALUES(1);
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifWHILE @rc * 2 <= @max
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifBEGIN
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif SET @rc = @rc * 2;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifEND
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifINSERT INTO dbo.Nums
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif--示例表
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifIF OBJECT_ID('dbo.Arrays') IS NOT NULL
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif DROP TABLE dbo.Arrays;
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifGO
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifCREATE TABLE dbo.Arrays
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif(
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif arrid VARCHAR(10) NOT NULL PRIMARY KEY,
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif array VARCHAR(8000) NOT NULL
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifINSERT INTO Arrays(arrid, array) VALUES('A', '20,22,25,25,14');
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifINSERT INTO Arrays(arrid, array) VALUES('B', '30,33,28');
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifINSERT INTO Arrays(arrid, array) VALUES('C', '12,10,8,12,12,13,12,14,10,9');
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifINSERT INTO Arrays(arrid, array) VALUES('D', '-4,-6,-4,-2');
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif--实施方法
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifSELECT arrid, substring(array,n,charindex(',',array+',',n)-n), n
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gifFROM dbo.Arrays
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif JOIN dbo.Nums
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif ON n <= LEN(array)
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif AND SUBSTRING(','+array, n, 1) = ','
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gifhttp://images.csdn.net/syntaxhighlighting/OutliningIndicators/ContractedBlock.gif/**//*
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gifselect arrid,charindex(',',array+',',n)-n from dbo.Arrays JOIN dbo.Nums
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif ON n <= LEN(array)
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif AND SUBSTRING(','+array, n, 1) = ','
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockEnd.gif*/
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gifhttp://images.csdn.net/syntaxhighlighting/OutliningIndicators/ContractedBlock.gif/**//*
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gifSELECT arrid, array, n
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gifFROM dbo.Arrays
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif JOIN dbo.Nums
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif ON n <= LEN(array)
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/InBlock.gif AND SUBSTRING(array, n, 1) = ','
http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockEnd.gif*/
页:
[1]