SQL 生成签名函数算法

A:

if object_id('dbo.fn_SQLSigTSQL') is not null
drop function dbo.fn_SQLSigTSQL;
GO
create function dbo.fn_SQLSigTSQL(@p1 ntext,@parselength int =4000)
returns nvarchar(4000)
as
begin
declare @maxlength as int;
declare @mode as char(10);
declare @p2 as nchar(4000);
declare @pos as int;
declare @currchar as char(1),@nextchar as char(1);
declare @p2len as int;
set @maxlength=len(rtrim(substring(@p1,1,4000)));
set @maxlength= case when @parselength>@maxlength then  @maxlength else @parselength end;
set @pos=1;
set @p2='';
set @p2len=0;
set @currchar='';
set @nextchar='';
set @mode='command';
while(@pos<=@maxlength)
begin
set @currchar=substring(@p1,@pos,1);
set @nextchar=substring(@p1,@pos+1,1);
if @mode='command'
begin
set @p2=left(@p2,@p2len)+@currchar;
set @p2len=@p2len+1;
if @currchar in(',','(',' ','=','<','>','!') and @nextchar between '0' and '9'
begin
set @mode='mumber';
set @p2=left(@p2,@P2len)+'#';
set @p2len=@p2len+1;
end
if @currchar=''''
begin
set @mode='literal';
set @p2=left(@p2,@p2len)+'#''';
set @p2len=@p2len+2;
end
end
else if @mode='mumber' and @nextchar in(',',')',' ','=','<','>','!')
set @mode='command';
else if @mode='literal' and @currchar=''''
set @mode='command';
set @pos=@pos+1;
end
return @p2;
end
GO

B:测试代码

select dbo.fn_SQLSigTSQL(N'select * from dbo.T1 where col=3 and col2>78',4000);

生成结果:

select * from dbo.T1 where col=# and col2>#