Код:
ALTER FUNCTION [dbo].[f_filter_convert]
(@var varchar(20),
@cd varchar(250))
RETURNS varchar(1024)
AS BEGIN
--функция преобразует фильтр Navision в операторы SQL для использования в WHERE
DECLARE @str varchar(1024);
DECLARE @i int, @pos int, @pos2 int;
DECLARE @tmp varchar(250);
IF @cd = '' RETURN @var+' LIKE ''%''';
SET @str = @cd;
SET @str = REPLACE(@str,'%','[%]');
SET @str = REPLACE(@str,'_','[_]');
SET @str = REPLACE(@str,'*','%');
SET @str = REPLACE(@str,'?','_');
SET @str = REPLACE(@str,'|',''' OR '+@var+' LIKE ''')
SET @str = REPLACE(@str,'&',''' AND '+@var+' LIKE ''')
SET @str = @var+' LIKE '''+@str+''''
SET @str = REPLACE(@str,')''',''')')
WHILE CHARINDEX(@var+' LIKE ''(',@str) <> 0
SET @str = REPLACE(@str,@var+' LIKE ''(','('+@var+' LIKE ''')
SET @str = REPLACE(@str,'LIKE ''<>','NOT LIKE ''')
--Заменяем NOT LIKE на <>, если нет масок % и _
SET @i = 1
SET @pos = 0
WHILE @i <> 0
BEGIN
SET @i = CHARINDEX('NOT LIKE ''',@str,@pos)
IF @i <> 0
BEGIN
SET @pos = CHARINDEX('''',@str,@i+10)
SET @tmp = SUBSTRING(@str,@i,@pos-@i+1)
IF CHARINDEX('%',@tmp) = 0 AND CHARINDEX('_',@tmp) = 0
BEGIN
SET @str = STUFF(@str,@i,@pos-@i+1,STUFF(@tmp,1,9,'<>'))
END
END
END
--Заменяем LIKE на =, если нет масок % и _
SET @i = 1
SET @pos = 0
WHILE @i <> 0
BEGIN
SET @i = CHARINDEX('LIKE ''',@str,@pos)
IF @i <> 0
BEGIN
SET @pos = CHARINDEX('''',@str,@i+6)
SET @tmp = SUBSTRING(@str,@i,@pos-@i+1)
IF CHARINDEX('%',@tmp) = 0 AND CHARINDEX('_',@tmp) = 0
BEGIN
SET @str = STUFF(@str,@i,@pos-@i+1,STUFF(@tmp,1,5,'='))
END
END
END
--Заменяем .. на BEETWEEN AND, если нет LIKE
SET @i = 1
SET @pos = 0
WHILE @i <> 0
BEGIN
SET @i = CHARINDEX(@var+' =''',@str,@pos)
IF @i <> 0
BEGIN
SET @pos = CHARINDEX('''',@str,@i+LEN(@var)+3)
SET @tmp = SUBSTRING(@str,@i,@pos-@i+1)
SET @pos2 = CHARINDEX('..',@tmp)
IF CHARINDEX('%',@tmp) = 0 AND CHARINDEX('_',@tmp) = 0 AND @pos2 <> 0
BEGIN
SET @tmp = @var+' BETWEEN '+SUBSTRING(@tmp,LEN(@var)+3,@pos2-LEN(@var)-3)+''' AND '''+SUBSTRING(@tmp,@pos2+2,LEN(@tmp))
SET @str = STUFF(@str,@i,@pos-@i+1,@tmp)
END
END
END
RETURN(@str);
END