Convertir filtro de navision a SQL

Navegando por en este blog encontre una funcion de SQL para convertir un filtro de navision en una clausula where de SQL. He modificado la funcion para que tenga en cuenta los condicionantes "| ". La cuelgo a continuacion:
ALTER FUNCTION [dbo].[CONVERT_NAV_FILTER]

(

-- Add the parameters for the function here

@parFieldName VARCHAR(MAX),

@parFilter VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)

AS

BEGIN



DECLARE @CurrentStringPOSPoints INT

DECLARE @CurrentStringPOSPipe INT

DECLARE @CurrentStringPOSAmp INT

DECLARE @CurrentStringPOSAt INT

DECLARE @CurrentStringPOSNot INT

DECLARE @CurrentStringPOSHaakOpen iNT

DECLARE @CurrentStringPOSHaaksluit int

DECLARE @CurrentStringPOSStar int

DECLARE @From VARCHAR(MAX)

DECLARE @To VARCHAR(MAX)

Declare @IndexToRead INT

Declare @ObjectToRead INT

Declare @IndexRead INT

Declare @ObjectRead INT

DECLARE @NextIndexToRead INT

Declare @NextObjectToRead INT

DECLARE @FirstRun INT

DECLARE @ReturnValue VARCHAR(MAX)

SET @ReturnValue = ''

SET @IndexToRead = 0

SET @CurrentStringPOSPoints = 0

SET @CurrentStringPOSPipe = 0

SET @CurrentStringPOSAmp = 0

SET @CurrentStringPOSAt = 0

SET @CurrentStringPOSNot = 0

SET @CurrentStringPOSHaakOpen = 0

SET @CurrentStringPOSHaaksluit = 0

SET @ObjectToRead = 0

SET @IndexRead = 0

SET @FirstRun = 1

WHILE 1 = 1

BEGIN

SET @NextIndexToRead = LEN(@parFilter)

SET @IndexToRead = LEN(@parFilter)

SET @ObjectToRead = 0

SET @NextObjectToRead = 0

--Find the index to read

IF @IndexToRead >= Charindex('..', @parFilter, @IndexRead + 1) and (Charindex('..', @parFilter, @IndexRead + 1) > 0)

BEGIN

SET @IndexToRead = Charindex('..', @parFilter, @IndexRead+1)

SET @ObjectToRead = 1

END

IF @IndexToRead >= Charindex('', @parFilter, @IndexRead + 1) and (Charindex('', @parFilter, @IndexRead + 1) > 0)

BEGIN

SET @IndexToRead = Charindex('', @parFilter, @IndexRead+1)

SET @ObjectToRead = 2

END

IF @IndexToRead >= Charindex('&', @parFilter, @IndexRead + 1) and (Charindex('&', @parFilter, @IndexRead + 1) > 0)

BEGIN

SET @IndexToRead = Charindex('&', @parFilter, @IndexRead+1)

SET @ObjectToRead = 3

END



IF @IndexToRead >= Charindex('|', @parFilter, @IndexRead + 1) and (Charindex('|', @parFilter, @IndexRead + 1) > 0)

BEGIN

SET @IndexToRead = Charindex('|', @parFilter, @IndexRead+1)

SET @ObjectToRead = 30

END



IF @IndexToRead >= Charindex('<>', @parFilter, @IndexRead + 1) and (Charindex('<>', @parFilter, @IndexRead + 1) > 0)

BEGIN

SET @IndexToRead = Charindex('<>', @parFilter, @IndexRead+1)

SET @ObjectToRead = 5

END

IF @IndexToRead >= Charindex('(', @parFilter, @IndexRead + 1) and (Charindex('(', @parFilter, @IndexRead + 1) > 0)

BEGIN

SET @IndexToRead = Charindex('(', @parFilter, @IndexRead +1)

SET @ObjectToRead = 6

END

IF @IndexToRead >= Charindex(')', @parFilter, @IndexRead + 1) and (Charindex(')', @parFilter, @IndexRead + 1) > 0)

BEGIN

SET @IndexToRead = Charindex(')', @parFilter, @IndexRead +1)

SET @ObjectToRead = 7

END

-- Find the next index to read

IF @NextIndexToRead >= Charindex('..', @parFilter, @IndexToRead + 1) and (Charindex('..', @parFilter, @IndexToRead + 1) > 0)

BEGIN

SET @NextIndexToRead = Charindex('..', @parFilter, @IndexToRead+1)

SET @NextObjectToRead = 1

END

IF @NextIndexToRead >= Charindex('', @parFilter, @IndexToRead + 1) and (Charindex('', @parFilter, @IndexToRead + 1) > 0)

BEGIN

SET @NextIndexToRead = Charindex('', @parFilter, @IndexToRead+1)

SET @NextObjectToRead = 2

END

IF @NextIndexToRead >= Charindex('&', @parFilter, @IndexToRead + 1) and (Charindex('&', @parFilter, @IndexToRead + 1) > 0)

BEGIN

SET @NextIndexToRead = Charindex('&', @parFilter, @IndexToRead+1)

SET @NextObjectToRead = 3

END

IF @NextIndexToRead >= Charindex('|', @parFilter, @IndexToRead + 1) and (Charindex('|', @parFilter, @IndexToRead + 1) > 0)

BEGIN

SET @NextIndexToRead = Charindex('|', @parFilter, @IndexToRead+1)

SET @NextObjectToRead = 30

END



IF @NextIndexToRead >= Charindex('<>', @parFilter, @IndexToRead + 1) and (Charindex('<>', @parFilter, @IndexToRead + 1) > 0)

BEGIN

SET @NextIndexToRead = Charindex('<>', @parFilter, @IndexToRead+1)

SET @NextObjectToRead = 5

END

IF @NextIndexToRead >= Charindex('(', @parFilter, @IndexToRead + 1) and (Charindex('(', @parFilter, @IndexToRead + 1) > 0)

BEGIN

SET @NextIndexToRead = Charindex('(', @parFilter, @IndexToRead+1)

SET @NextObjectToRead = 6

END

IF @NextIndexToRead >= Charindex(')', @parFilter, @IndexToRead + 1) and (Charindex(')', @parFilter, @IndexToRead + 1) > 0)

BEGIN

SET @NextIndexToRead = Charindex(')', @parFilter, @IndexToRead+1)

SET @NextObjectToRead = 7

END

-- *********************************************

-- Checks Complete start converting

-- *********************************************



-- If this is the first time the conversion is done and there is a or & sign,

-- Set a filter on the first part

IF @FirstRun = 1 and @IndexToRead > 1

BEGIN

IF @ObjectToRead = 2 --

BEGIN

SET @From = Substring(@parFilter, 1, @IndexToRead - 1)

if charindex('@', @From, 0) > 0

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') OR '

else

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') OR '

else

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') OR '

else

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') OR '

END

IF @ObjectToRead = 3 -- &

BEGIN

SET @From = Substring(@parFilter, 1, @IndexToRead - 1)

if charindex('@', @From, 0) > 0

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') AND '

else

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') AND '

else

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') AND '

else

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') AND '

END

IF @ObjectToRead = 30 -- |

BEGIN

SET @From = Substring(@parFilter, 1, @IndexToRead - 1)

if charindex('@', @From, 0) > 0

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') OR '

else

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') OR '

else

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') OR '

else

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') OR '

END

END

SET @FirstRun = 0

IF @ObjectToRead = 1 -- 1 = ..

BEGIN

SET @From = Substring(@parFilter, @IndexRead + 1, @IndexToRead - (@IndexRead + 1))

IF @nextobjecttoread = 0

SET @To = substring(@parFilter, @IndexToRead + 2 , @NextIndexToRead -(@IndexToRead + 1))

else

SET @To = substring(@parFilter, @IndexToRead + 2 , @NextIndexToRead -(@IndexToRead + 2))

if charindex('@', @From, 0) > 0 or charindex('@', @To, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') BETWEEN ''' + lower(replace(@From, '@', '')) + ''' AND ''' + lower(replace(@To,'@','')) + ''')'

ELSE

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' BETWEEN ''' + @From + ''' AND ''' + @To + ''')'

IF @NextObjectToRead = 2 --

SET @ReturnValue = @ReturnValue + ' OR '

IF @NextObjectToRead = 3 -- &

SET @ReturnValue = @ReturnValue + ' AND '



IF @NextObjectToRead = 30 -- &

SET @ReturnValue = @ReturnValue + ' OR '

END

IF @ObjectToRead = 2 -- 2 =

BEGIN

IF @NextObjectToRead <> 6 and @NextObjectToRead <> 5 and @NextObjectToRead <> 1

BEGIN

if @NextObjectToRead = 0

SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - @IndexToRead)

else

SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - (@IndexToRead + 1))

if charindex('@', @From, 0) > 0

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''')'

else

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''')'

else

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''')'

else

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''')'

END

IF @NextObjectToRead = 2 --

SET @ReturnValue = @ReturnValue + ' OR '

IF @NextObjectToRead = 3 -- &

SET @ReturnValue = @ReturnValue + ' AND '



IF @NextObjectToRead = 30 -- &

SET @ReturnValue = @ReturnValue + ' OR '

END

IF @ObjectToRead = 3 -- 3 = &

BEGIN

IF @NextObjectToRead <> 6 and @NextObjectToRead <> 5 and @NextObjectToRead <> 1

BEGIN

if @NextObjectToRead = 0

SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - @IndexToRead)

else

SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - (@IndexToRead + 1))

if charindex('@', @From, 0) > 0

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%') , '@', '')) + ''')'

else

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''')'

else

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''')'

else

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''')'

END

IF @NextObjectToRead = 2 --

SET @ReturnValue = @ReturnValue + ' OR '

IF @NextObjectToRead = 3 -- &

SET @ReturnValue = @ReturnValue + ' AND '



IF @NextObjectToRead = 30 -- &

SET @ReturnValue = @ReturnValue + ' OR '

END

IF @ObjectToRead = 30 -- 3 = &

BEGIN

IF @NextObjectToRead <> 6 and @NextObjectToRead <> 5 and @NextObjectToRead <> 1

BEGIN

if @NextObjectToRead = 0

SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - @IndexToRead)

else

SET @From = substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - (@IndexToRead + 1))

if charindex('@', @From, 0) > 0

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%') , '@', '')) + ''')'

else

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''')'

else

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''')'

else

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''')'

END

IF @NextObjectToRead = 2 --

SET @ReturnValue = @ReturnValue + ' OR '

IF @NextObjectToRead = 3 -- &

SET @ReturnValue = @ReturnValue + ' AND '



IF @NextObjectToRead = 30 -- &

SET @ReturnValue = @ReturnValue + ' OR '

END



IF @ObjectToRead = 4 -- 4 = @

BEGIN

SET @ReturnValue = @ReturnValue

END

IF @ObjectToRead = 5 -- 5 = <>

BEGIN

SET @ReturnValue = @ReturnValue + ' NOT '

SET @From = substring(@parFilter, @IndexToRead + 2, @NextIndexToRead - (@IndexToRead + 2))

if charindex('@', @From, 0) > 0

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''')'

else

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''')'

else

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''')'

else

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''')'

IF @NextObjectToRead = 2 --

SET @ReturnValue = @ReturnValue + ' OR '

IF @NextObjectToRead = 3 -- &

SET @ReturnValue = @ReturnValue + ' AND '

END

IF @ObjectToRead = 6 -- 6 = (

BEGIN

SET @ReturnValue = @ReturnValue + '('

SET @From = Substring(@parFilter, @IndexToRead + 1, @NextIndexToRead - (@IndexToRead +1))

IF @NextObjectToRead = 2 --

BEGIN

if charindex('@', @From, 0) > 0

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') OR '

else

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') OR '

else

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') OR '

else

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') OR '

END

IF @NextObjectToRead = 3 -- &

BEGIN

if charindex('@', @From, 0) > 0

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') AND '

else

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') AND '

else

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') AND '

else

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') AND '

ENd



IF @NextObjectToRead = 30 -- &

BEGIN

if charindex('@', @From, 0) > 0

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@From, '*', '%'), '@', '')) + ''') OR '

else

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@From, '@', '')) + ''') OR '

else

if charindex('*', @From, 0) > 0

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@From, '*', '%') + ''') OR '

else

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @From + ''') OR '

ENd

END

IF @ObjectToRead = 7 -- 7 = )

BEGIN

SET @ReturnValue = @ReturnValue + ')'

IF @NextObjectToRead = 2 --

SET @ReturnValue = @ReturnValue + ' OR '

IF @NextObjectToRead = 3 -- &

SET @ReturnValue = @ReturnValue + ' AND '



IF @NextObjectToRead = 30 -- &

SET @ReturnValue = @ReturnValue + ' OR '

END

IF @ObjectToRead = 8 -- 8 = *

BEGIN

SET @ReturnValue = @ReturnValue

END

-- Nothing found? => BREAK loop

IF @IndexToRead = LEN(@parFilter)

BREAK

SET @ObjectRead = @ObjectToRead

SET @IndexRead = @IndexToRead

SET @IndexToRead = @NextIndexToRead

END -- LOOP

IF @ReturnValue = ''

BEGIN

if charindex('@', @parFilter, 0) > 0

if charindex('*', @parFilter, 0) > 0

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') LIKE ''' + lower(replace(replace(@parFilter, '*', '%'), '@', '')) + ''')'

else

SET @ReturnValue = @ReturnValue + '(lower(' + @parFieldName + ') = ''' + lower(replace(@parFilter, '@', '')) + ''')'

else

if charindex('*', @parFilter, 0) > 0

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' LIKE ''' + replace(@parFilter, '*', '%') + ''')'

else

SET @ReturnValue = @ReturnValue + '(' + @parFieldName + ' = ''' + @parFilter + ''')'

END

-- Return the result of the function

RETURN '(' + @ReturnValue + ')'

END