IN statement in SQL doesn't accept a wild character (SAP)

The IN statement in SQL doesn't accept wild characters directly. When working with SAP systems, you need to use OR/AND conditions to handle wildcard functionality with the IN operator.

Workaround Solution

To implement wildcard behavior with the IN statement, you can use a conditional approach that checks for a wildcard parameter first, then applies the IN condition ?

select *
from Test1 t INNER JOIN Test2 s ON t.ID = s.RID
where t.sdate >= ?1
AND t.edate 

In this approach, the condition ('%' = ?4 OR t.afdeling IN (?4)) works as follows ?

• If parameter ?4 equals '%', the condition evaluates to true (wildcard behavior)
• If parameter ?4 contains specific values, the IN condition is evaluated

Parameter Position Alternative

If you are using parameter positions to pass parameters and need separate positions for the wildcard check and IN condition, modify it as follows ?

AND ('%' = ?4 OR t.afdeling IN (?5))
AND ('%' = ?6 OR s.sid IN (?7))

This separates the wildcard parameter from the IN parameter, giving you more control over parameter binding in your SAP application.

Conclusion

Since SQL's IN statement doesn't support wildcards directly, using conditional OR logic with wildcard checks provides an effective workaround for implementing flexible filtering in SAP environments.