Remove SQL/user defined variables in where clause without broken condition expression

Sometimes you want to modify condition expression in where clause, such as remove a SQL variable in where condition without broken that condition expression or remove all where clause if necessary.

Take this SQL for example, you want to remove @var1 in where condition, only delete it from where clause doesn’t work, that will make this statement syntax invalid.

select f from tb
where f1 between 1 and @var1

Illegal statement after remove @var1

select f from tb
where f1 between 1 and 

Desired statement after remove @var1, should remove all where clause.

select f from tb

Here is another sample, if you want to remove @var2 in where condition, only delete it from where clause doesn’t work, that will make this statement syntax invalid.

select f from tb
where f1>10 and (@var1+1 between a and @var2)

Illegal statement after remove @var1

select f from tb
where f1>10 and (@var1+1 between a and )

Desired statement after remove @var2

select f from tb
where f1>10

Download this demo: C# version, Java version

update or remove a user defined variable

Take this sql for example:

SELECT SUM (d.amt) 
FROM   summit.cntrb_detail d 
WHERE  d.id = summit.mstr.id 
       AND d.system_gift_type IN ( 'OG', 'PLP', 'PGP' ) 
       AND d.fund_coll_attrb IN ( '$Institute$' ) 
       AND d.fund_acct IN ( '$Fund$' ) 
       AND d.cntrb_date >= '$From_Date$' 
       AND d.cntrb_date <= '$Thru_Date$' 
GROUP  BY d.id 

In this query, values between two $ e.g '$XXX$' represents some run time fields which user enter. For example '$Fund$' will be replaced with some user entered vale as '0000001'.

Now our requirement is that we need to remove complete join in case user has not entered value specific to $XXX$ run time field.

Consider example as if user has not entered '$Fund$' value the we should remove complete join "and d.FUND_ACCT in ( '$Fund$' ) " from this query.

General SQL Parser can help you to achieve this quite easily with this demo. And even better, this demo can remove "Where" clause if and only if single run time condition is presented and user has not provided any input for it.

Download this demo: C# version, Java version

Here is another example that illustrate how to iterate expression in pre-order, in-order and post-order.