Convert a Navision Filter to a SQL Where clause


I wrote this SQL script a while ago.
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
Advertisements
Tagged with: ,
Posted in NAV
8 comments on “Convert a Navision Filter to a SQL Where clause
  1. seza2783 says:

    Hello,

    Nice job man!

    There is one mistake. Your script does not handle NAV simbol ‘|’, which means OR.
    I little bit modified your script.

    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

  2. TO says:

    Hallo,

    that’s a fantastic function!

    I add the ‘?’
    I think now it’s complete?!
    Or is anything missing?

    /****** Object: UserDefinedFunction [dbo].[CONVERT_NAV_FILTER] Script Date: 08/09/2011 14:41:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    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 begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) LIKE ”’ + lower(replace(replace(@From, ‘*’, ‘%’), ‘@’, ”)) + ”’) OR ‘
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) = ”’ + lower(replace(@From, ‘@’, ”)) + ”’) OR ‘
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else
    if charindex(‘*’, @From, 0) > 0
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘*’, ‘%’) + ”’) OR ‘
    else
    if charindex(‘?’, @From, 0) > 0 –TO
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘?’, ‘_’) + ”’) OR ‘ –TO
    else — TO
    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 begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) LIKE ”’ + lower(replace(replace(@From, ‘*’, ‘%’), ‘@’, ”)) + ”’) AND ‘
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) = ”’ + lower(replace(@From, ‘@’, ”)) + ”’) AND ‘
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else
    if charindex(‘*’, @From, 0) > 0
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘*’, ‘%’) + ”’) AND ‘
    else
    if charindex(‘?’, @From, 0) > 0 –TO
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘?’, ‘_’) + ”’) AND ‘ –TO
    else — TO
    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 begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) LIKE ”’ + lower(replace(replace(@From, ‘*’, ‘%’), ‘@’, ”)) + ”’)’
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) = ”’ + lower(replace(@From, ‘@’, ”)) + ”’)’
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else
    if charindex(‘*’, @From, 0) > 0
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘*’, ‘%’) + ”’)’
    else
    if charindex(‘?’, @From, 0) > 0 –TO
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘?’, ‘_’) + ”’)’ –TO
    else — TO
    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 begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) LIKE ”’ + lower(replace(replace(@From, ‘*’, ‘%’) , ‘@’, ”)) + ”’)’
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) = ”’ + lower(replace(@From, ‘@’, ”)) + ”’)’
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else
    if charindex(‘*’, @From, 0) > 0
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘*’, ‘%’) + ”’)’
    else
    if charindex(‘?’, @From, 0) > 0 –TO
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘?’, ‘_’) + ”’)’ –TO
    else — TO
    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 begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) LIKE ”’ + lower(replace(replace(@From, ‘*’, ‘%’), ‘@’, ”)) + ”’)’
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) = ”’ + lower(replace(@From, ‘@’, ”)) + ”’)’
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else
    if charindex(‘*’, @From, 0) > 0
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘*’, ‘%’) + ”’)’
    else
    if charindex(‘?’, @From, 0) > 0 –TO
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘?’, ‘_’) + ”’)’ –TO
    else — TO
    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 begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) LIKE ”’ + lower(replace(replace(@From, ‘*’, ‘%’), ‘@’, ”)) + ”’) OR ‘
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) = ”’ + lower(replace(@From, ‘@’, ”)) + ”’) OR ‘
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else
    if charindex(‘*’, @From, 0) > 0
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘*’, ‘%’) + ”’) OR ‘
    else
    if charindex(‘?’, @From, 0) > 0 –TO
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘?’, ‘_’) + ”’) OR ‘ –TO
    else — TO
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ = ”’ + @From + ”’) OR ‘

    END
    IF @NextObjectToRead = 3 — &
    BEGIN
    if charindex(‘@’, @From, 0) > 0
    if charindex(‘*’, @From, 0) > 0 begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) LIKE ”’ + lower(replace(replace(@From, ‘*’, ‘%’), ‘@’, ”)) + ”’) AND ‘
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) = ”’ + lower(replace(@From, ‘@’, ”)) + ”’) AND ‘
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else
    if charindex(‘*’, @From, 0) > 0
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘*’, ‘%’) + ”’) AND ‘
    else
    if charindex(‘?’, @From, 0) > 0 –TO
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@From, ‘?’, ‘_’) + ”’) AND ‘ –TO
    else — TO
    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 begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) LIKE ”’ + lower(replace(replace(@parFilter, ‘*’, ‘%’), ‘@’, ”)) + ”’)’
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else begin
    SET @ReturnValue = @ReturnValue + ‘(lower(‘ + @parFieldName + ‘) = ”’ + lower(replace(@parFilter, ‘@’, ”)) + ”’)’
    if charindex(‘?’, @parFilter, 0) > 0
    SET @ReturnValue = replace(@ReturnValue,’?’, ‘_’)
    end
    else
    if charindex(‘*’, @parFilter, 0) > 0
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@parFilter, ‘*’, ‘%’) + ”’)’
    else
    if charindex(‘?’, @From, 0) > 0 –TO
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ LIKE ”’ + replace(@parFilter, ‘?’, ‘_’) + ”’)’ –TO
    else — TO
    SET @ReturnValue = @ReturnValue + ‘(‘ + @parFieldName + ‘ = ”’ + @parFilter + ”’)’
    END

    — Return the result of the function
    RETURN ‘(‘ + @ReturnValue + ‘)’
    END

  3. Lukas Res says:

    Hello,

    It´s looks like amazing but how I can use result in sql select? I have for example table G_L Entry and table AccountFilter where I have result of your scritp in one column. Thank you

  4. Michael Hartl says:

    great work – tried to add the > and < but I'm getting in conflict with … any ideas ? thanks michael

  5. Michael Hartl says:

    …in conflict with the unequal sign ()

  6. Przemyslaw Remin says:

    Can one of you please put the final function with pipeline | sign in a text file somewhere to be downloaded. The later functions have a wrong coding.

  7. Milcho says:

    Hi Przemyslaw, send me a message (mdimkov@hotmail.com) and I’ll send you the complete version – I was just able to fix it but can’t be pasted here, as HTML destroys the > and ‘ signs

  8. leon šinko says:

    How I can use result in sql select?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: