declare @string varchar(1000) set @string='aaaa,bbbb,cccc,dddd' set @string='select '''+replace(@string,',','''as str union all select ''')+'''' exec(@string)
declare @aa varchar(100),@bb int,@cc int set @aa='aaaa-bb-ca s a'set @bb=0 set @cc=charindex('-',@aa) select substring(@aa,@bb,@cc-@bb) while @cc>0 begin set @bb=@cc+1 set @cc=charindex('-',@aa,@bb) select substring(@aa,@bb,case when @cc>0 then @cc-@bb else len(@aa) end) end
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] GOCREATE 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 ENDif @teststring1 <> '' INSERT INTO TABLE1 (COL1) VALUES (@teststring1) go
declare @string varchar(1000) set @string='aaaa,bbbb,cccc,dddd' set @string='select * into ##tmp from (select '''+replace(@string,',','''as str union all select ''')+''') a' exec(@string) select * from ##tmp drop table ##tmp
set @string='aaaa,bbbb,cccc,dddd'
set @string='select '''+replace(@string,',','''as str union all select ''')+''''
exec(@string)
set @aa='aaaa-bb-ca s a'set @bb=0
set @cc=charindex('-',@aa)
select substring(@aa,@bb,@cc-@bb)
while @cc>0
begin
set @bb=@cc+1
set @cc=charindex('-',@aa,@bb)
select substring(@aa,@bb,case when @cc>0 then @cc-@bb else len(@aa) end)
end
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]
GOCREATE 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
ENDif @teststring1 <> '' INSERT INTO TABLE1 (COL1) VALUES (@teststring1)
go
set @string='aaaa,bbbb,cccc,dddd'
set @string='select * into ##tmp from (select '''+replace(@string,',','''as str union all select ''')+''') a'
exec(@string)
select * from ##tmp
drop table ##tmp