set nocount on go --处理当前串中的空格,对时分秒的处理 createfunction f_castdt(@svarchar(8000)) returnsvarchar(1000) as begin set@s=ltrim(@s) WHILECHARINDEX('',@s)>0 BEGIN SET@s=replace(@s,'','') END set@s=casewhenpatindex('%-[0-9][0-9] [0-9][0-9]:%',@s)>0thenstuff(@s,patindex('%-[0-9][0-9] [0-9][0-9]:%',@s)+3,1,'*') whenpatindex('%-[0-9][0-9] [0-9]:%',@s)>0thenstuff(@s,patindex('%-[0-9][0-9] [0-9]:%',@s)+3,1,'*') whenpatindex('%-[0-9] [0-9][0-9]:%',@s)>0thenstuff(@s,patindex('%-[0-9] [0-9][0-9]:%',@s)+2,1,'*') whenpatindex('%-[0-9] [0-9]:%',@s) >0thenstuff(@s,patindex('%-[0-9] [0-9]:%',@s)+2,1,'*') else@send return@s end go --加一个分隔函数: createfunction F_split( @svarchar(8000), --包含多个数据项的字符串 @posint, --要获取的数据项的位置 @splitvarchar(10) --数据分隔符 )RETURNSvarchar(100) AS BEGIN IF@sISNULLRETURN(NULL) DECLARE@splitlenint--分隔符长度 SELECT@splitlen=LEN(@split+'a')-2 WHILE@pos>1ANDcharindex(@split,@s+@split)>0 SELECT@pos=@pos-1, @s=stuff(@s,1,charindex(@split,@s+@split)+@splitlen,'') RETURNreplace((nullif(left(@s,charindex(@split,@s+@split)-1),'')),char(13),'') END GO --判断当前串是有字母如果有那么当作varchar createfunction f_ischar(@svarchar(100)) returnsbit as begin ifpatindex('%[a-zA-Z]%',@s)>0 return1 return0 end go -- 循环截取法 CREATEFUNCTION f_splitSTR( @svarchar(8000), --待分拆的字符串 @splitvarchar(20) --数据分隔符 )RETURNS@reTABLE(col varchar(100)) AS BEGIN DECLARE@splitlenint set@s=ltrim(rtrim(@s)) SET@splitlen=LEN(@split+'a')-2 WHILECHARINDEX(@split,@s)>0 BEGIN INSERT@reVALUES( rtrim(ltrim(LEFT(@s,CHARINDEX(@split,@s)-1))) ) SET@s=ltrim(STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')) END INSERT@reVALUES(@s) RETURN END GO createproc sp_autoscript @svarchar(8000) as declare@avarchar(8000),@bvarchar(8000),@cvarchar(8000) set@a=rtrim(left(@s,charindex(char(13),@s) -1)) set@s=right(@s,len(@s) -charindex(char(13),@s)-1) set@b=rtrim(left(@s,charindex(char(13),@s) -1)) set@s=right(@s,len(@s) -charindex(char(13),@s)-1) set@c=rtrim(left(@s,charindex(char(13),@s) -1)) set@s=left(@s,len(@s) -1) createtable #tmp1(px intidentity(1,1),col varchar(120),col2 varchar(50),col3 varchar(50)) createtable #tmp2(px intidentity(1,1),col varchar(120)) createtable #tmp3(px intidentity(1,1),col varchar(1000)) insertinto #tmp1(col) selectltrim(rtrim(col)) from f_splitSTR(@b,'') set@c= dbo.f_castdt(@c) insertinto #tmp2 selectltrim(rtrim(col)) from f_splitSTR(@c,'') insertinto #tmp3 selectltrim(rtrim(col)) from f_splitSTR(@s,' ') --取每一列的最大值,尽量让类型准确 --update a --set col= c.col --from #tmp2 a left join ( --select a.px, max(dbo.F_split(b.col,a.px,' ')) as col --from #tmp2 a,#tmp3 b --group by a.px) c on a.px = c.px declare@pxint,@colvarchar(100),@maxidint select@maxid=count(1) from #tmp1 DECLARE f CURSORFORSELECT px,col FROM #tmp2 OPEN f FETCHNEXTFROM f INTO@px,@col WHILE@@FETCH_STATUS=0 BEGIN set@col=--还原datatime数据用于判断 casewhenpatindex('%-[0-9][0-9]*[0-9][0-9]:%',@col)>0thenstuff(@col,patindex('%-[0-9][0-9]*[0-9][0-9]:%',@col)+3,1,'') whenpatindex('%-[0-9][0-9]*[0-9]:%',@col)>0thenstuff(@col,patindex('%-[0-9][0-9]*[0-9]:%',@col)+3,1,'') whenpatindex('%-[0-9]*[0-9][0-9]:%',@col)>0thenstuff(@col,patindex('%-[0-9]*[0-9][0-9]:%',@col)+2,1,'') whenpatindex('%-[0-9]*[0-9]:%',@col) >0thenstuff(@col,patindex('%-[0-9]*[0-9]:%',@col)+2,1,'') else@colend ifISDATE(@col) =1 iflen(@col) <=10 update #tmp1 set col = col +' smalldatetime,', col2 =casewhen px =1then' select '''''else''''''end, col3 =casewhen px =@maxidthen''''' union all'else''''','end where px =@px else update #tmp1 set col = col +' datetime,', col2 =casewhen px =1then' select '''''else''''''end, col3 =casewhen px =@maxidthen''''' union all'else''''','end where px =@px else ifisnumeric(@col) =1 ifcharindex('.',@col) >0 update #tmp1 set col = col +' numeric('+ltrim(len(@col) -1)+','+ltrim(len(@col) -charindex('.',@col))+'),', col2 =casewhen px =1then' select 'else''end, col3 =casewhen px =@maxidthen' union all'else','end where px =@px else update #tmp1 set col = col +' int,', col2 =casewhen px =1then'select 'else''end, col3 =casewhen px =@maxidthen' union all'else','end where px =@px else if dbo.f_ischar(@col) =1 update #tmp1 set col = col +' varchar('+ltrim(len(@col))+'),', col2 =casewhen px =1then' select '''''else''''''end, col3 =casewhen px =@maxidthen''''' union all'else''''','end where px =@px FETCHNEXTFROM f INTO@px,@col END CLOSE f DEALLOCATE f update #tmp3 set col = dbo.f_castdt(col) --select * from #tmp3 declare@sqlvarchar(8000) declare@sql1varchar(8000) select@sql=isnull(@sql+'+' ,'')+'''' +col2 +'''+dbo.F_split(col,'+ltrim(px)+','''')'+'+'''+col3+'''' from #tmp1 select@sql1=isnull(@sql1,'') + col from #tmp1 --select @sql exec('update #tmp3 set col = '+@sql) --还原datetime数据 update #tmp3 set col = casewhenpatindex('%-[0-9][0-9]*[0-9][0-9]:%',col)>0thenstuff(col,patindex('%-[0-9][0-9]*[0-9][0-9]:%',col)+3,1,'') whenpatindex('%-[0-9][0-9]*[0-9]:%',col)>0thenstuff(col,patindex('%-[0-9][0-9]*[0-9]:%',col)+3,1,'') whenpatindex('%-[0-9]*[0-9][0-9]:%',col)>0thenstuff(col,patindex('%-[0-9]*[0-9][0-9]:%',col)+2,1,'') whenpatindex('%-[0-9]*[0-9]:%',col) >0thenstuff(col,patindex('%-[0-9]*[0-9]:%',col)+2,1,'') else col end set@sql='' set@sql='/* -- Author:Flystone -- Version:V1.001 Date:2008-05-15 初稿 -- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常 -- 2、 增加了形如yyyy-mm-dd hh:mm:ss -- yyyy-m-d h:m:s 格式的处理 */'+char(10)+ ' -- Test Data: '+@a+char(10)+ 'If object_id('''+@a+''') is not null '+char(13)+ ' Drop table '+@a+char(10)+ 'Go'+char(13)+ 'Create table '+@a+'('+left(@sql1,len(@sql1) -1)+')'+char(10)+ 'Go' +char(10)+ 'Insert into '+@a+' ' select@sql=@sql+ col +char(13) from #tmp3 set@sql=left(@sql,len(@sql) -10) set@sql=@sql+char(13) + 'Go'+char(10)+ '--Start'+char(10)+ 'Select * from '+@a+char(10)+' --Result: /* */ --End ' print@sql droptable #tmp1,#tmp2,#tmp3 go declare@svarchar(8000) set@s='Ta ID Name dt 1 aa 2001-01-01 00:01:02 2 bb 2001-1-1 00:01:02 3 cc 2001-01-01 0:01:02 4 dd 2001-1-1 0:1:2 ' exec sp_autoscript @s dropfunction f_splitSTR,f_ischar,f_castdt,F_split dropproc sp_autoscript set nocount off
运行结果
/**//* -- Author:Flystone -- Version:V1.001 Date:2008-05-15 初稿 -- Version:V1.002 Date:2008-05-16 1、 处理空格带来的异常 -- 2、 增加了形如yyyy-mm-dd hh:mm:ss -- yyyy-m-d h:m:s 格式的处理 */ -- Test Data: Ta Ifobject_id('Ta') isnotnull Droptable Ta Go Createtable Ta(ID int,Name varchar(2),dt datetime) Go Insertinto Ta select1,'aa','2001-01-01 00:01:02'unionall select2,'bb','2001-1-1 00:01:02'unionall select3,'cc','2001-01-01 0:01:02'unionall select4,'dd','2001-1-1 0:1:2' Go --Start Select*from Ta --Result: /**//* ID Name dt ----------- ---- ------------------------------------------------------ 1 aa 2001-01-01 00:01:02.000 2 bb 2001-01-01 00:01:02.000 3 cc 2001-01-01 00:01:02.000 4 dd 2001-01-01 00:01:02.000 (所影响的行数为 4 行) */ --End