MS_SQL中分割字符串

use tempdb

--建立临时表保存各字符串
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TABLE1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TABLE1]
GO

CREATE TABLE [dbo].[TABLE1] (
 [COL1] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

declare @teststring varchar(1000)
declare @teststring1 varchar(200)    --一个分隔的字符串
declare @testi1 int  --现检查第几位字符，逗号位置
set @teststring = '1,22,333,4444,55555'
set @testi1 = 1
set @teststring1 = ''
print len(@teststring)
WHILE @testi1 <= len(@teststring)
BEGIN
    if SUBSTRING(@teststring, @testi1, 1) = ','
        BEGIN
            INSERT INTO TABLE1 (COL1) VALUES (@teststring1)
            set @teststring1 = ''
        END
    else
        BEGIN
            set @teststring1 = @teststring1 + SUBSTRING(@teststring, @testi1, 1)
        END
    set @testi1 = @testi1 + 1
END

if @teststring1 <> '' INSERT INTO TABLE1 (COL1) VALUES (@teststring1)
go