How to use LIKE condition in SQL with numeric field?

Question

Hi,

I'm using this query to get some specific data: "select * from emp where emp_name LIKE 's%'";

emp_nam is character field, how can I use the same logic condition with numeric field? something like:

"select * from emp where emp_id ????

where emp_id is numeric field.

Thanks,

Answer 1

Use cast or convert function on the emp_id field and you can compare with like.

Answer 2

No, you can't use LIKE for numeric fields. Try using <,> or =, >=, <= ;)

If you want to search in a numeric field for things like "beginning with 12" or sth like this, your design may not fit to your needs.

Answer 3

You can't do a wildcard on a number, however, if you really need to, you can convert the number to a varchar and then perform the wildcard match.

eg.

SELECT * FROM emp WHERE CONVERT(varchar(20), emp_id) LIKE '1%'

Answer 4

In Access database engine SQL syntax, to use the % wildcard character EITHER you must be using ANSI-92 Query Mode OR use the ALIKE keyword in place of the LIKE keyword.

For details of ANSI-92 Query Mode see About ANSI SQL query mode (MDB) in the Access2003 Help (the same will apply to ACE in Access2007 but they removed the topic from the Access2007 Help for some reason). If you doing this in code you will need to use OLE DB e.g. ADO classic in VBA.

For the ALIKE keyword... you won't find much. It's one of those officially undocumented features, meaning there is an element of risk that it may be removed from a future revision to the Access database engine. Personally, I'd take that risk over having to explicitly code for both ANSI-89 Query Mode and ANSI-92 Query Mode as is necessary for Validation Rules and CHECK constraints (see example below). Coding for both can be done but it is more long winded and tricky to get right i.e. has more immediate risk if you get it wrong.

That's the answer. Now for the 'solution'...

Clearly, if you need to perform that kind of query on emp_id then the domain is wrong i.e. it shouldn't be a numeric field.

Cure the disease: change the schema to make this a text field, adding a domain rule ensuring it only contains numeric characters e.g.

CHECK (emp_id NOT LIKE '%[^0-9]%')

EDIT the 'Jet' tag has now been added. The above CHECK constraint needs to be rewritten because the Access database engine has its own syntax: replace the ^ character with !. Also, to make this compatible with both ANSI-89 Query Mode and ANSI-92 Query Mode, use the ALIKE keyword i.e.

CHECK (emp_id NOT ALIKE '%[!0-9]%')

Answer 5

In Access you can concatenate the numeric field with an empty string to coerce it into a string that you can compare using LIKE:

select * from emp where emp_id & '' like '123*'

Also note that Access uses * not % as the wildcard character. See: Like Operator (Microsoft Access SQL).