By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,203 Members | 1,401 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,203 IT Pros & Developers. It's quick & easy.

using IN with LIKE

P: n/a
Given the following statement:

SELECT * FROM tbl
WHERE [tbl].[column] IN ('a', 'b', 'c');

I want to be able to continue using IN so how could I change it so
that each of the values passed to IN are being handled as such:

'a' becomes LIKE "*" & "a" & "*"

TIA,
Chas
Apr 11 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
ChasW wrote:
Given the following statement:

SELECT * FROM tbl
WHERE [tbl].[column] IN ('a', 'b', 'c');

I want to be able to continue using IN so how could I change it so
that each of the values passed to IN are being handled as such:

'a' becomes LIKE "*" & "a" & "*"


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You can't use wildcards in the IN predicate. You'd have to use ORs:

WHERE tbl.column Like "*a*"
OR tbl.column Like "*b*"
OR tbl.column Like "*c*"

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBRDw+KoechKqOuFEgEQJNFgCg6LRsG1YzZVoVckTM+Dt11P e9wG4AoPSj
kc9L4BB8r1gtLIIKu4yX0V/X
=mtEB
-----END PGP SIGNATURE-----
Apr 11 '06 #2

P: n/a
MGFoster schreef:
ChasW wrote:
Given the following statement:

SELECT * FROM tbl
WHERE [tbl].[column] IN ('a', 'b', 'c');

I want to be able to continue using IN so how could I change it so
that each of the values passed to IN are being handled as such:

'a' becomes LIKE "*" & "a" & "*"


You can't use wildcards in the IN predicate. You'd have to use ORs:


In a way you can. Pass the values to IN via some table literals(literal),
then:

WHERE [tbl].[column] IN
(SELECT [tbl].[column] FROM literals
WHERE [tbl].[column] LIKE '*' & literals.literal & '*');

That's pretty lame - almost any other way would be more practical.
If values are only single letters, simply:

WHERE [tbl].[column] LIKE "*[abc]*"

--
Paul
Apr 12 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.