SQL LIKE doesn't find obvious matches

Question

I'm writing the following SQL query:

SELECT *
FROM OS
WHERE OS.VERSION LIKE '%1%';

In my table there are rows with char 1 in it. However, it returns an empty result. I changed a little bit the LIKE clause to different values, but it still doesn't work.

What can I do to fix that?

Well, like the LIKE operator *does* work .. so review your expectations. (And give us reason to believe in your expectations - i.e. provide data/schema or a SQL fiddle.) — user2864740, May 12 '14 at 19:57
Which database are you using, what does your schema look like, what data is in the table? — dennislloydjr, May 12 '14 at 19:58
Related: http://stackoverflow.com/questions/719115/microsoft-jet-wildcards-asterisk-or-percentage-sign — Brad, May 12 '14 at 20:04

score 6 · Accepted Answer · answered May 12 '14 at 19:59

6

Try double-quotes and * for wildcards. You are using Oracle syntax instead of Access syntax.

answered May 12 '14 at 19:59

Tripp Kinetics

5,178
2
23
37

It'd be more correct to simply say MS-Access doesn't support standard SQL `LIKE` syntax. Even SQL Server supports it properly: http://technet.microsoft.com/en-us/library/ms179859.aspx – May 12 '14 at 20:04
@ebyrob While the default setting in Access is to use `*` (which is consistent with how you do the same operation in VBA) You can also use `%` if you set your queries to be made in compliance with ANSI-92 SQL. JET, the db under Access, pre-dates SQL-Server so it's not surprising they get to do their own thing a bit here... – Brad May 12 '14 at 20:10

score 0 · Answer 2 · answered May 12 '14 at 19:58

0

LIKE operation can't be used with columns of integer type. I assume that OS.Version is of integer type?

Edit1: If you are referring to MS Access then you have to do the LIKE with stars (*) instead of %.

answered May 12 '14 at 19:58

Thanos Markou

2,587
3
25
32

SQL LIKE doesn't find obvious matches

2 Answers2