FormatSQL
USE [DEV]
GO
/****** Object: UserDefinedFunction [dbo].[FormatSQL] Script Date: 12/21/2011 17:32:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[FormatSQL](@str varchar(200))
returns varchar(200)
as
begin
declare @retStr varchar(200)
set @retStr = @str;
if charindex('..',@str,0) > 0
begin
declare @str1 varchar(20),@str2 varchar(20)
declare @i int
--set @str = 'abc..d'
set @i = charindex('..',@str,0)
set @str1 = substring(@str,1,@i-1);
set @str2 = substring(@str,@i+2,len(@str)-@i+1);
set @retStr = ' between ''' + @str1 + ''' and ''' + @str2 + ''' ';
return @retStr;
end
if charindex(',',@str,0) > 0
begin
set @str = ' in' + dbo.FormatStr(@str);
return @str;
end
if @str = '!'''''
begin
set @str = ' <> ''''';
return @str;
end
if @str = ''''''
begin
set @str = ' = '''' ';
return @str;
end
if charindex('>',@str,1) = 1
begin
set @str = ' > ''' + Substring(@str,2,len(@str)-1) + '''';
return @str;
end
if charindex('<',@str,1) = 1
begin
set @str = ' < ''' + substring(@str,2,len(@str)-1) + '''';
return @str;
end
if charindex('!',@str,1) = 1
begin
set @str = ' <> ''' + substring(@str,2,len(@str)-1) + '''';
return @str;
end
set @retStr = replace(@retStr ,'*','%');
set @retStr = replace(@retStr ,'?','_');
set @retStr = replace(@retStr ,'!','^');
set @retStr = ' like ''' + @retStr + '''';
return @retStr
end