My question is that how to search the database by SQL statement. The following will not work in this case. I am using VBscript and MS SQL database.
SQL = "Select * from Products where DESCRIPTION LIKE '%" & ColorC & "%'"
The number of colors are not fixed, there could be 1, 2, 3, 5, 6 colors as selected by a user. And it has LIKE as it finds colors in product description. How does IN work in this case.
declare @ColorC varchar(50) = 'Red,blue,'
declare @sql varchar(1000)
create table #bab22(id int identity(1,1), vehicle varchar(50), color varchar(50))
insert into #bab22
select 'Milenial Falcon', 'Red' union
select 'Benzo', 'Black' union
select 'Teshla', 'Blue'
set @sql = 'select * From #bab22 where color in (' + replace(QUOTENAME(@ColorC,''''),',', ''',''') + ')'
select @sql
exec(@sql)
drop table #bab22