It will convert most of the Navision filters into a SQL WHERE clause.
@ will be converted to lower(..)
* will result in a LIKE %
.. will result in a BETWEEN clause
& = AND
= OR
<> = NOT
Also brackets are supported.
To use this script you should pass the fieldname and the filter to this function.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- ===============================================================
-- Author: Kenny Vaes
-- Create date: 19/03/2007
-- Description: Converts a Navision Filter to a SQL Where clause
-- ===============================================================
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 = 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 = 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
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 '
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 '
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 '
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
END
IF @ObjectToRead = 7 -- 7 = )
BEGIN
SET @ReturnValue = @ReturnValue + ')'
IF @NextObjectToRead = 2 --
SET @ReturnValue = @ReturnValue + ' OR '
IF @NextObjectToRead = 3 -- &
SET @ReturnValue = @ReturnValue + ' AND '
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
Posts
Geen opmerkingen:
Een reactie posten