LIKE wildcards in SQL

Question

I am writing a piece of code where @Variant is provided. I wanted to store multiple values by capturing them in a variable (runtime) and pass them in the WHERE clause. Share the logic

DECLARE @Variant VARCHAR(50)
SET @Variant='''25'',''33'''
SELECT @Variant  ---- which is actually '25','33'
SELECT 'TRUE' WHERE '25' IN (@Variant)

DECLARE @Variant VARCHAR(50)
SET @Variant='''25'',''33'''
SELECT @Variant  ---- which is actually '25','33'
SELECT 'TRUE' WHERE '25' IN (SELECT @Variant)

Above queries do not work...

--DECLARE @Variant VARCHAR(50)
--SET @Variant='''25'',''33'''
--SELECT @Variant
SELECT 'TRUE' WHERE '25' IN ('25','33')

Above is a simpler query in SQL Server -which I tried if SQL accepts. My actual query is to pass wild cards in the LIKE statement in WHERE clause.

DECLARE @Variant VARCHAR(50) SET @Variant='''25%'' OR [Item_No] LIKE ''33%''' SELECT @Variant
SELECT * from Table1 WHERE [Item_No] LIKE (@Variant) --I presume @variable will be replaced with its value during execution.

Anyways, simpler way - I have a Item_No in the Where clause to which i want to supply multiple wild cards. Is there a method similar to inline function, which should replace the text at the @variant and execute combining it.

Tag the dbms used. (That code doesn't look like ANSI SQL at all...) — jarlh, Jun 21 '16 at 07:59
This has nothing to do with `LIKE` or wildcards. Fix the tags please. — Pred, Jun 21 '16 at 08:07
Looks like the title does not really match the question. Same goes for the tags. — ArBro, Jun 21 '16 at 08:10
oracle doesn't support select without from clause if that is the case. — kostas, Jun 21 '16 at 08:10
Based on the syntax, this is SQL Server, and if so, a possible duplicate of http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x or any of the similar questions. — Pred, Jun 21 '16 at 08:25

score 2 · Answer 1 · answered Jun 21 '16 at 08:02

DECLARE @Variant VARCHAR(50)
SET @Variant='''25'',''33'''

At this point @Variant contains a single string.

SELECT 'TRUE' WHERE '25' IN (@Variant)

Given the 25 does not equal '25','33' this is never going to match. (IN is looking for an exact match – equality – with at least one of the set of values supplied in the parentheses.)

SQL will not magically parse a string into a collection. You'll need to tell it to. Options to do this depend on the particular DBMS you are using. They include temporary tables and table valued variables.

Thanks Richard - your explanation "Given the 25 does not equal '25','33' this is never going to match" — Ravi, Jun 22 '16 at 10:05

score 0 · Answer 2 · answered Jun 21 '16 at 08:10

0

SQL Like condition will work in your scenerio.

DECLARE @Variant VARCHAR(50)
SET @Variant='''25'',''33'''

SELECT 'TRUE' WHERE @Variant like '%25%'

Here result is TRUE. Hope this will help.

answered Jun 21 '16 at 08:10

Tasawar

541
4
20

1

And what if `@Variant = '25,33,255,256'` ? – sagi Jun 21 '16 at 08:12

LIKE wildcards in SQL

2 Answers2