How to use LIKE condition in SQL with numeric field?

Question

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

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 2

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).

Answer 3

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 not fits your needs.

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

'%' wild card worked for me on MS -SQL server. See http://technet.microsoft.com/en-us/library/aa933232%28v=sql.80%29.aspx .

The query select * from MyTable where ID like '548%' works on MS-SQL Server 2008 R2 and returns results with ids 5481,5485 etc. ID column is int type.

Answer 6

using CONCAT implic convert integer to string

SELECT * FROM city WHERE CONCAT(id_city,'') LIKE '%119%'

Answer 7

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

Answer 8

Hi I had trouble doing this with a float I had to cast two tims cast(cast(EmpId as bigint) as varchar(15)) like '%903%' Hope someone find this helpful

Answer 9

In FileMaker SQL you can cast any field to a string with the STRVAL function:

SELECT * FROM emp WHERE STRVAL(emp_id) LIKE '1%'

---

Here is a real example of the use of this in FileMaker:

How to list all records which contain an ASCII 0 in a numeric field

SELECT emp_id FROM emp WHERE STRVAL(someNumericField) LIKE '%'+CHR(0)+'%'

Answer 10

Write a numeric expression inside the Where clause.

Ex: To select numeric Col1 ending with last 2 digits as 20:

WHERE Column1-(Column1/100) = 20
(Divide by 100 is integer division). 

To select when Col1 should begin with 50:

WHERE ( Col1 >= 500 and Col1 <= 509) OR ( Col1 >= 5000 and Col1 <= 5099)  
OR ( Col1 >= 50000 and Col1 <= 50999) etc. (depending on the numeric column 
width). 

ALternatively: floor (log10 (abs (Col1))) + 1 returns the number of digits in a number. So, the below might be the required clause.

  WHERE Col1> 0 AND Col1 / ((floor (log10 (abs (x))) + 1 - 2)  * 100)= 50 

(The -2 is to get the first 2 digits of Col1).

Answer 11

This worked for me (MySql):

SELECT * FROM TABLA_UNO WHERE CAST(NRO_ID AS VARCHAR(12)) LIKE '%355%'; (NRO_ID is INTEGER DATA TYPE)

SELECT * FROM ASIENTOS WHERE CAST(COTIZACION AS VARCHAR(12)) LIKE '%40.11%'; (COTIZACION is DECIMAL DATA TYPE)

SELECT * FROM ASIENTOS WHERE CAST(FECHA AS VARCHAR(12)) LIKE '%2023%'; (FECHA is DATE)