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.