substitue wildcard in SQL server?

Question

I am trying to figure out what I can do with SQL server as I am modifying reports in ERP using SQL server 2019.

Was wondering if the "in" keyword in where clause could act as a like statement and not like an = with a bunch of "or"s statements. Is it possible to use wildcards like % _ or even the [character or string]

In my ERP when I run this leaving the parameter will be treated as as '' statement.
WHERE T2.[PriceList] ='1'
and t0.cardcode IN ('[%0]', '[%1]', '[%2]')
and t0.ValidFor='Y'

Other report leaving the parameter blank will return all results. Could I use a wildcard with an equal or is it only with like statement
WHERE T1.Quantity != 0
AND T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and
('[%3]' <> '' AND T3.GroupName = '[%3]' OR '[%3]' ='')

Answer 1

If you need to treat your parameters as like statements, e.g. where groupName like '%abc% or groupname like '%efg%' then you may try

declare @t table (parameterValue varchar(30))
insert into @t (parameterValue)
select * from values (('[%1]'),('[%2]'),('[%3]')) x (val) ; -- guessing the syntax of using parameters here

select *
from myTable t inner joing @t par on t.GroupName like '%' + t.ParameterValue + '%'
where ...

Answer 2

Was wondering if the "in" keyword in where clause could act as a like statement and not like an = with a bunch of "or"s statements.

No. col IN (@a, @b, @c) is just syntactic sugar for col = @a OR col = @b OR col = @c.

Your post is not entirely clear. You talk about wildcards, but the %1 etc you have there has no relation to wildcards. That seems to be some special stuff that your ERP system is using:

WHERE T1.Quantity != 0

AND T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and
('[%3]' <> '' AND T3.GroupName = '[%3]' OR '[%3]' ='')

So what happen here depends what your ERP system is doing. If it just replaces the placeholders with strings maybe. That is, it must actually replace them with empty strings, then it may work. But if it smart and use NULL instead, no. And it uses parameters starting @, you will run into data type issues.

In short, you will have to try on your own, and if you get problems, you will need to consult a forum for your ERP system where people may know the details of these placeholders.

Answer 3

Hi，@Jonathan Brotto

Welcome to Microsoft T-SQL Q&A Forum!

As the two experts said, whether the where condition can retrieve fields in TSQL depends entirely on whether the specific value of the field behind IN is empty.
In your ERP system, %0-2 should represent different attributes. Regarding wildcards tsql uses where columnname like %..%, but I'm sorry that we can't test the results for your ERP, because there are many types of ERP systems, and their syntax is different. For a better explanation of this problem, you need to ask ERP forum for better help.

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.