sql 分割

[wwfgu00ing]

一个参数@str_fieldsList 是要操作数据库表字段与值的集合 格式为: 字段类型_字段_值!字段类型_字段_值。。。中间是以！分割的

现拼接sql语句

例如

@str_fieldsList ='str_QQ_232323![e-mail]!int_num_123'

拼接后的sql语句为 update table1 set QQ='232323',email='[e-mail]',num=123

[chuanzhang5687]

可以自己写个分割函数！

[--小F--]

不要发重复的帖子嘛

[hao1hao2hao3]

建议通过程序处理。Split函数之类的。

[--小F--]

if object_id('f_split')is not null drop function  f_split
go
create function f_split
(
@s     varchar(8000),  --待分拆的字符串
@split varchar(10)     --数据分隔符
)returns table
as
 return
 (
  select substring(@s,number,charindex(@split,@s+@split,number)-number)as col
  from master..spt_values
  where type='p' and number<=len(@s+'a') 
  and charindex(@split,@split+@s,number)=number
  )
go
select * from dbo.f_split('11,2,3',',')
/*
col
----
11
2
3
(3 個資料列受到影響)
*/

[dawugui]

declare @str_fieldsList as varchar(100)
set @str_fieldsList ='str_QQ_232323![e-mail]!int_num_123'
declare @sql as varchar(100)
set @sql = 'update table1 set '
set @sql = @sql + replace(parsename(replace(parsename(replace(replace(@str_fieldsList,'.','='),'!','.'),3),'_','.'),2),'=','.') + ' = '
set @sql = @sql + '''' + replace(parsename(replace(parsename(replace(replace(@str_fieldsList,'.','='),'!','.'),3),'_','.'),1),'=','.') + '''' + ','
set @sql = @sql + replace(parsename(replace(parsename(replace(replace(@str_fieldsList,'.','='),'!','.'),2),'_','.'),2),'=','.') + ' = '
set @sql = @sql + '''' + replace(parsename(replace(parsename(replace(replace(@str_fieldsList,'.','='),'!','.'),2),'_','.'),1),'=','.') + '''' + ','
set @sql = @sql + replace(parsename(replace(parsename(replace(replace(@str_fieldsList,'.','='),'!','.'),1),'_','.'),2),'=','.') + ' = '
set @sql = @sql + replace(parsename(replace(parsename(replace(replace(@str_fieldsList,'.','='),'!','.'),1),'_','.'),1),'=','.') 

print @sql

/*
update table1 set QQ = '232323',email = '[e-mail]',num = 123
*/

[快溜]

declare @str_fieldsList varchar(100)
set @str_fieldsList ='str_QQ_232323![e-mail]!int_num_123'
set @str_fieldsList='update table1 set '+replace(replace(replace(
                  replace(@str_fieldsList,'str_',''),'int_',''),'!',''','),'_','=''')+''''
print @str_fieldsList
exec(@str_fieldsList)
/*
update table1 set QQ='232323',email='[e-mail]',num='123'

[q465897859]

帖子重复了

[AcHerat]

declare @str_fieldsList varchar(8000)
set @str_fieldsList ='str_QQ_232323![e-mail]!int_num_123'

select st,left(st,charindex('_',st)-1) sjl,
	substring(st,charindex('_',st)+1,charindex('_',st,charindex('_',st)+1)-charindex('_',st)-1) col,
	right(st,len(st)-charindex('_',st,charindex('_',st)+1)) val
  into #tb
from(
	select substring(@str_fieldsList,number,charindex('!',@str_fieldsList+'!',number)-number) st
	from master..spt_values
	where [type] = 'p' and number between 1 and len(@str_fieldsList)
		and substring('!'+@str_fieldsList,number,1) = '!'
) t

select *
from #tb

select stuff((select ','+col+'='+(case when sjl = 'str' then ''''+ltrim(val)+''''
		when sjl = 'int' then ltrim(val) else null end) from #tb for xml path('')),1,1,'')

drop table #tb

/***********

QQ='232323',email='[e-mail]',num=123

(1 行受影响)

[q465897859]

declare @str varchar(max)='str_QQ_232323![e-mail]!int_num_123'
declare @qq varchar(20),@email varchar(20),@num varchar(20),@sql varchar(max)
select @qq=substring(@str,CHARINDEX('_',@str,5)+1,CHARINDEX('!',@str+'!')-CHARINDEX('_',@str,5)-1)
set @str=SUBSTRING(@str,CHARINDEX('!',@str+'!')+1,LEN(@str)-CHARINDEX('!',@str+'!'))
select @email=substring(@str,CHARINDEX('_',@str,5)+1,CHARINDEX('!',@str+'!')-CHARINDEX('_',@str,5)-1)
set @str=SUBSTRING(@str,CHARINDEX('!',@str+'!')+1,LEN(@str)-CHARINDEX('!',@str+'!'))
select @num=substring(@str,CHARINDEX('_',@str,5)+1,CHARINDEX('!',@str+'!')-CHARINDEX('_',@str,5)-1)
set @sql='update table1 set QQ='+@qq+',email='+@email+',num='+@num+''
print @sql