Wild card search in optional macro prompt in SQL

[sreevp2020]

Hi Guys,

I have requirement like :select * from table where date=?dateparameter? and  (name='SAM%' or name='PAUL%' or name='RAM%').

I am not able to append that "%" at end of parameter value after passing in sql through macro prompt . Please help!

I tried as below:

select * from table where date=#prompt('dateparam','string') #promptmany('namepara','string',' ','and name in (','%',')')#-->did not work

[bdbits]

Syntax issues are part of the problem... Let's say dateparam is '2016-07-01' and namepara is 'JOE'. I believe your expression would resolve to:
select * from table where date='2016-07-01'  'JOE' and name in (','%',')'

You don't need to specify string datatypes, that is the default. So my initial pass says something more like this:
select * from table where date=#prompt('dateparam')# and name in #promptmany('namepara')#

Obviously that does not have the wildcards inserted. promptmany really only works with in, and does not support wildcards, so promptmany is not going to solve this all by itself. I found an approach from CognosPaul here on cognoise at http://www.cognoise.com/index.php?topic=22355.0. Seems reasonable, and he's the master of #prompt macros among many other things. So something like...

select * from table where date=#prompt('dateparam')#
and name in #join('%'' and [name] like ''%' + split(';',promptmany('namepara','token','','''%','','%'''))#

I am not somewhere I can test this but that should give you the general idea.