字符拆分网上一大堆你自己去看看 我以前写过拆分的函数 类似于 '1,2,4,5,71' 你自己去改写下 CREATE Function [dbo].[AccountToTable](@str varchar(1000)) Returns @tableName Table ( account varchar(50) ) As --该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列,例如字符串'1,2,3,4,5' 将编程一个表,这个表 Begin set @str = @str+',' Declare @insertStr varchar(50) --截取后的第一个字符串 Declare @newstr varchar(1000) --截取第一个字符串后剩余的字符串 set @insertStr = left(@str,charindex(',',@str)-1) set @newstr = stuff(@str,1,charindex(',',@str),'') Insert @tableName Values(@insertStr) while(len(@newstr)>0) begin set @insertStr = left(@newstr,charindex(',',@newstr)-1) Insert @tableName Values(@insertStr) set @newstr = stuff(@newstr,1,charindex(',',@newstr),'') end Return End
declare @s varchar(1000) set @s='1,27,a' declare @sql varchar(1000) set @sql='select col='''+replace(@s,',','''union all select ''')+'''' exec(@sql)
CREATE function Get_StrArrayLength( @str varchar(1024), --要分割的字符串 @split varchar(10) --分隔符号)returns intasbegin declare @location int declare @start int declare @length int set @str=ltrim(rtrim(@str)) set @location=charindex(@split,@str) set @length=1 while @location<>0 begin set @start=@location+1 set @location=charindex(@split,@str,@start) set @length=@length+1 end return @lengthend调用示例:select dbo.Get_StrArrayLength('78,1,2,3',',')
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(100)) returns @temp table(F1 varchar(100)) as begin declare @ch as varchar(100) set @SourceSql=@SourceSql+@StrSeprate while(@SourceSql<>'') begin set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1) insert @temp values(@ch) set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'') end return end
if exists(select * from sysobjects where [name]='tb') drop table tb go create table tb ( ID int, COL text ) go insert into tb(ID,COL) ( select 1,'A2E8147B B2E81CCC A2AAAA7B A2EEEEEC' ) go SELECT a.ID, b.COL FROM( SELECT ID, COL = CONVERT(xml,' <root> <v>' + REPLaCE(CONVERT(varchar(100),COL), ' ', ' </v> <v>') + ' </v> </root>') FROM tb )a OUTER APPLY( SELECT COL = N.v.value('.', 'varchar(100)') FROM a.COL.nodes('/root/v') N(v) )b 结果: ID COL ----------- ---------------------------------------------------------------------------------------------------- 1 A2E8147B 1 B2E81CCC 1 A2AAAA7B 1 A2EEEEEC
CREATE TABLE #TB(ID INT,COL TEXT) INSERT #TB SELECT 1,'A2E8147B B2E81CCC A2AAAA7B A2EEEEEC' SELECT * FROM #TB DECLARE @A VARCHAR(1000) DECLARE @B VARCHAR(2000) SET @A='A2E8147B B2E81CCC A2AAAA7B A2EEEEEC' SELECT REPLACE(@A,' ',''' UNION ALL SELECT ''') SET @B='SELECT ID=1,COL= '''+REPLACE(@A,' ',''' UNION ALL SELECT ID=1,COL= ''')+'''' SELECT @B EXEC(@B) 混分
我以前写过拆分的函数
类似于 '1,2,4,5,71' 你自己去改写下
CREATE Function [dbo].[AccountToTable](@str varchar(1000))
Returns @tableName Table
(
account varchar(50)
)
As
--该函数用于把一个用逗号分隔的多个数据字符串变成一个表的一列,例如字符串'1,2,3,4,5' 将编程一个表,这个表
Begin
set @str = @str+','
Declare @insertStr varchar(50) --截取后的第一个字符串
Declare @newstr varchar(1000) --截取第一个字符串后剩余的字符串
set @insertStr = left(@str,charindex(',',@str)-1)
set @newstr = stuff(@str,1,charindex(',',@str),'')
Insert @tableName Values(@insertStr)
while(len(@newstr)>0)
begin
set @insertStr = left(@newstr,charindex(',',@newstr)-1)
Insert @tableName Values(@insertStr)
set @newstr = stuff(@newstr,1,charindex(',',@newstr),'')
end
Return
End
declare @s varchar(1000)
set @s='1,27,a'
declare @sql varchar(1000)
set @sql='select col='''+replace(@s,',','''union all select ''')+''''
exec(@sql)
CREATE function Get_StrArrayLength( @str varchar(1024), --要分割的字符串 @split varchar(10) --分隔符号)returns intasbegin declare @location int declare @start int declare @length int set @str=ltrim(rtrim(@str)) set @location=charindex(@split,@str) set @length=1 while @location<>0 begin set @start=@location+1 set @location=charindex(@split,@str,@start) set @length=@length+1 end return @lengthend调用示例:select dbo.Get_StrArrayLength('78,1,2,3',',')
create function f_splitstr(@SourceSql varchar(8000),@StrSeprate varchar(100)) returns @temp table(F1 varchar(100)) as begin declare @ch as varchar(100) set @SourceSql=@SourceSql+@StrSeprate while(@SourceSql<>'') begin set @ch=left(@SourceSql,charindex(',',@SourceSql,1)-1) insert @temp values(@ch) set @SourceSql=stuff(@SourceSql,1,charindex(',',@SourceSql,1),'') end return end
if exists(select * from sysobjects where [name]='tb')
drop table tb
go
create table tb
(
ID int,
COL text
)
go
insert into tb(ID,COL)
(
select 1,'A2E8147B B2E81CCC A2AAAA7B A2EEEEEC'
)
go
SELECT a.ID, b.COL
FROM(
SELECT ID, COL = CONVERT(xml,' <root> <v>' + REPLaCE(CONVERT(varchar(100),COL), ' ', ' </v> <v>') + ' </v> </root>') FROM tb
)a
OUTER APPLY(
SELECT COL = N.v.value('.', 'varchar(100)') FROM a.COL.nodes('/root/v') N(v)
)b
结果:
ID COL
----------- ----------------------------------------------------------------------------------------------------
1 A2E8147B
1 B2E81CCC
1 A2AAAA7B
1 A2EEEEEC
INSERT #TB SELECT 1,'A2E8147B B2E81CCC A2AAAA7B A2EEEEEC'
SELECT * FROM #TB
DECLARE @A VARCHAR(1000)
DECLARE @B VARCHAR(2000)
SET @A='A2E8147B B2E81CCC A2AAAA7B A2EEEEEC'
SELECT REPLACE(@A,' ',''' UNION ALL SELECT ''')
SET @B='SELECT ID=1,COL= '''+REPLACE(@A,' ',''' UNION ALL SELECT ID=1,COL= ''')+''''
SELECT @B
EXEC(@B)
混分