Преобразование фильтра Nav в SQL

[grif]

Немного конечно через зад, но работает

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