Version:V1.002
Date:2008-05-16
修改描述:
1、 处理空格带来的异常
2、 增加了形如yyyy-mm-dd hh:mm:ss
yyyy-m-d h:m:s 格式的处理
set nocount on
go
-- 处理当前串中的空格,对时分秒的处理
create function f_castdt( @s varchar ( 8000 ))
returns varchar ( 1000 )
as
begin
set @s = ltrim ( @s )
WHILE CHARINDEX ( ' ' , @s ) > 0
BEGIN
SET @s = replace ( @s , ' ' , ' ' )
END
set @s = case when patindex ( ' %-[0-9][0-9] [0-9][0-9]:% ' , @s ) > 0 then stuff ( @s , patindex ( ' %-[0-9][0-9] [0-9][0-9]:% ' , @s ) + 3 , 1 , ' * ' )
when patindex ( ' %-[0-9][0-9] [0-9]:% ' , @s ) > 0 then stuff ( @s , patindex ( ' %-[0-9][0-9] [0-9]:% ' , @s ) + 3 , 1 , ' * ' )
when patindex ( ' %-[0-9] [0-9][0-9]:% ' , @s ) > 0 then stuff ( @s , patindex ( ' %-[0-9] [0-9][0-9]:% ' , @s ) + 2 , 1 , ' * ' )
when patindex ( ' %-[0-9] [0-9]:% ' , @s ) > 0 then stuff ( @s , patindex ( ' %-[0-9] [0-9]:% ' , @s ) + 2 , 1 , ' * ' ) else @s end
return @s
end
go
-- 加一个分隔函数:
create function F_split(
@s varchar ( 8000 ), -- 包含多个数据项的字符串
@pos int , -- 要获取的数据项的位置
@split varchar ( 10 ) -- 数据分隔符
) RETURNS varchar ( 100 )
AS
BEGIN
IF @s IS NULL RETURN ( NULL )
DECLARE @splitlen int -- 分隔符长度
SELECT @splitlen = LEN ( @split + ' a ' ) - 2
WHILE @pos > 1 AND charindex ( @split , @s + @split ) > 0
SELECT @pos = @pos - 1 ,
@s = stuff ( @s , 1 , charindex ( @split , @s + @split ) + @splitlen , '' )
RETURN replace (( nullif ( left ( @s , charindex ( @split , @s + @split ) - 1 ), '' )), char ( 13 ), '' )
END
GO
-- 判断当前串是有字母如果有那么当作varchar
create function f_ischar( @s varchar ( 100 ))
returns bit
as
begin
if patindex ( ' %[a-zA-Z]% ' , @s ) > 0
return 1
return 0
end
go
-- 循环截取法
CREATE FUNCTION f_splitSTR(
@s varchar ( 8000 ), -- 待分拆的字符串
@split varchar ( 20 ) -- 数据分隔符
) RETURNS @re TABLE (col varchar ( 100 ))
AS
BEGIN
DECLARE @splitlen int
set @s = ltrim ( rtrim ( @s ))
SET @splitlen = LEN ( @split + ' a ' ) - 2
WHILE CHARINDEX ( @split , @s ) > 0
BEGIN
INSERT @re VALUES ( rtrim ( ltrim ( LEFT ( @s , CHARINDEX ( @split , @s ) - 1 ))) )
SET @s = ltrim ( STUFF ( @s , 1 , CHARINDEX ( @split , @s ) + @splitlen , '' ))
END
INSERT @re VALUES ( @s )
RETURN
END
GO
create proc sp_autoscript
@s varchar ( 8000 )
as
declare @a varchar ( 8000 ), @b varchar ( 8000 ), @c varchar ( 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 )
create table #tmp1(px int identity ( 1 , 1 ),col varchar ( 120 ),col2 varchar ( 50 ),col3 varchar ( 50 ))
create table #tmp2(px int identity ( 1 , 1 ),col varchar ( 120 ))
create table #tmp3(px int identity ( 1 , 1 ),col varchar ( 1000 ))
insert into #tmp1(col) select ltrim ( rtrim (col)) from f_splitSTR( @b , ' ' )
set @c = dbo.f_castdt( @c )
insert into #tmp2 select ltrim ( rtrim (col)) from f_splitSTR( @c , ' ' )
insert into #tmp3 select ltrim ( 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 @px int , @col varchar ( 100 ), @maxid int
select @maxid = count ( 1 ) from #tmp1
DECLARE f CURSOR FOR SELECT px,col FROM #tmp2
OPEN f
FETCH NEXT FROM f INTO @px , @col
WHILE @@FETCH_STATUS = 0
BEGIN
set @col = -- 还原datatime数据用于判断
case when patindex ( ' %-[0-9][0-9]*[0-9][0-9]:% ' , @col ) > 0 then stuff ( @col , patindex ( ' %-[0-9][0-9]*[0-9][0-9]:% ' , @col ) + 3 , 1 , ' ' )
when patindex ( ' %-[0-9][0-9]*[0-9]:% ' , @col ) > 0 then stuff ( @col , patindex ( ' %-[0-9][0-9]*[0-9]:% ' , @col ) + 3 , 1 , ' ' )
when patindex ( ' %-[0-9]*[0-9][0-9]:% ' , @col ) > 0 then stuff ( @col , patindex ( ' %-[0-9]*[0-9][0-9]:% ' , @col ) + 2 , 1 , ' ' )
when patindex ( ' %-[0-9]*[0-9]:% ' , @col ) > 0 then stuff ( @col , patindex ( ' %-[0-9]*[0-9]:% ' , @col ) + 2 , 1 , ' ' ) else @col end
if ISDATE ( @col ) = 1
if len ( @col ) <= 10
update #tmp1
set col = col + ' smalldatetime, ' ,
col2 = case when px = 1 then ' select ''''' else '''''' end ,
col3 = case when px = @maxid then ''''' union all ' else ''''' , ' end
where px = @px
else
update #tmp1
set col = col + ' datetime, ' ,
col2 = case when px = 1 then ' select ''''' else '''''' end ,
col3 = case when px = @maxid then ''''' union all ' else ''''' , ' end
where px = @px
else
if isnumeric ( @col ) = 1
if charindex ( ' . ' , @col ) > 0
update #tmp1
set col = col + ' numeric( ' + ltrim ( len ( @col ) - 1 ) + ' , ' + ltrim ( len ( @col ) - charindex ( ' . ' , @col )) + ' ), ' ,
col2 = case when px = 1 then ' select ' else '' end ,
col3 = case when px = @maxid then ' union all ' else ' , ' end
where px = @px
else
update #tmp1
set col = col + ' int, ' ,
col2 = case when px = 1 then ' select ' else '' end ,
col3 = case when px = @maxid then ' union all ' else ' , ' end
where px = @px
else
if dbo.f_ischar( @col ) = 1
update #tmp1
set col = col + ' varchar( ' + ltrim ( len ( @col )) + ' ), ' ,
col2 = case when px = 1 then ' select ''''' else '''''' end ,
col3 = case when px = @maxid then ''''' union all ' else ''''' , ' end
where px = @px
FETCH NEXT FROM f INTO @px , @col
END
CLOSE f
DEALLOCATE f
update #tmp3
set col = dbo.f_castdt(col)
-- select * from #tmp3
declare @sql varchar ( 8000 )
declare @sql1 varchar ( 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 =
case when patindex ( ' %-[0-9][0-9]*[0-9][0-9]:% ' ,col) > 0 then stuff (col, patindex ( ' %-[0-9][0-9]*[0-9][0-9]:% ' ,col) + 3 , 1 , ' ' )
when patindex ( ' %-[0-9][0-9]*[0-9]:% ' ,col) > 0 then stuff (col, patindex ( ' %-[0-9][0-9]*[0-9]:% ' ,col) + 3 , 1 , ' ' )
when patindex ( ' %-[0-9]*[0-9][0-9]:% ' ,col) > 0 then stuff (col, patindex ( ' %-[0-9]*[0-9][0-9]:% ' ,col) + 2 , 1 , ' ' )
when patindex ( ' %-[0-9]*[0-9]:% ' ,col) > 0 then stuff (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
drop table #tmp1,#tmp2,#tmp3
go
declare @s varchar ( 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
drop function f_splitSTR,f_ischar,f_castdt,F_split
drop proc 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
If object_id ( ' Ta ' ) is not null
Drop table Ta
Go
Create table Ta(ID int ,Name varchar ( 2 ),dt datetime )
Go
Insert into Ta
select 1 , ' aa ' , ' 2001-01-01 00:01:02 ' union all
select 2 , ' bb ' , ' 2001-1-1 00:01:02 ' union all
select 3 , ' cc ' , ' 2001-01-01 0:01:02 ' union all
select 4 , ' 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
-- 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
If object_id ( ' Ta ' ) is not null
Drop table Ta
Go
Create table Ta(ID int ,Name varchar ( 2 ),dt datetime )
Go
Insert into Ta
select 1 , ' aa ' , ' 2001-01-01 00:01:02 ' union all
select 2 , ' bb ' , ' 2001-1-1 00:01:02 ' union all
select 3 , ' cc ' , ' 2001-01-01 0:01:02 ' union all
select 4 , ' 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