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.
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.
SELECT * FROM emp WHERE CONVERT(varchar(20), emp_id) LIKE '1%'
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).
No, you can’t use
LIKE for numeric fields.
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.
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
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.
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]%')
‘%’ wild card worked for me on MS -SQL server. See http://technet.microsoft.com/en-us/library/aa933232%28v=sql.80%29.aspx .
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
convert function on the
emp_id field and you can compare with
using CONCAT implic convert integer to string
SELECT * FROM city WHERE CONCAT(id_city,”) LIKE ‘%119%’
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
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)+'%'
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
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 first 2 digits of Col1).