Like Operator for Integer

Like operator for integer

If you must use LIKE, you can cast your number to char/varchar, and perform the LIKE on the result. This is quite inefficient, but since LIKE has a high potential of killing indexes anyway, it may work in your scenario:

... AND CAST(phone AS VARCHAR(9)) LIKE '%0203'

If you are looking to use LIKE to match the beginning or the end of the number, you could use integer division and modulus operators to extract the digits. For example, if you want all nine-digit numbers starting in 407, search for

phone / 1000000 = 407

Filtering with Like operator on integer column

You can use custom SQL fragments in the query. To get around strictly typed FilterQuery, you can use expr which is just an identity function (returns its parameter), so have effect only for TS checks.

Something like this should work:

import { expr } from '@mikro-orm/core';

const res = await repo.findAndCount({
  [expr('cast(srNumber as text)')]: { $like: '%1000%' },
}, options);

https://mikro-orm.io/docs/entity-manager/#using-custom-sql-fragments

SQL LIKE condition to check for integer?

That will select (by a regex) every book which has a title starting with a number, is that what you want?

SELECT * FROM books WHERE title ~ '^[0-9]'

if you want integers which start with specific digits, you could use:

SELECT * FROM books WHERE CAST(price AS TEXT) LIKE '123%'

or use (if all your numbers have the same number of digits (a constraint would be useful then))

SELECT * FROM books WHERE price BETWEEN 123000 AND 123999;

Use LIKE '%' with integer in SQL

The LIKE operator is a string function. It requires a string input to test against, and will attempt to automatically convert input values to string (varchar or nvarchar) before doing the comparison. Because of this implicit conversion you can use if for numbers.

The problem in your code is that you are adding an int and a varchar. Try rewriting your stored procedure as:

CREATE PROCEDURE AutoSearchTeamByMobile
    @Team_Mobile int
AS
SELECT * FROM Team_Info WHERE Team_Mobile LIKE CAST(@Team_Mobile AS NVARCHAR) + '%';

Casting @Team_Mobile to varchar will make the + operator function correctly, and should give you the results you're after.

One style point though...

Storing phone numbers as integers is potentially problematic for various reasons. At some point you are going to encounter a phone number that can't be stored in the limited range of an integer. I have thousands of phone numbers in one of my databases that are 16 characters in length, an even when I strip the international dial specifier off them they don't fit in an integer.

Store your phone numbers as varchar or nvarchar and you'll have a much easier time of things in general.

How to specify a Like on an integer column?

Unfortunately, you didn't specify what database you're using (SQL is just the query language....), but if you're on SQL Server (the Microsoft RDBMS product), then you could create a computed column of type VARCHAR(15) to hold a string representation of your INT, and then just search on that....

ALTER TABLE dbo.YourTable
   ADD IdAsString AS CAST(Id AS VARCHAR(15)) PERSISTED    -- PERSISTED might not work - depending on your version of SQL Server

SELECT (list of columns)
FROM dbo.YourTable
WHERE IdAsString LIKE '123%'

Whether that really makes business sense, is a totally different story..... (I agree with Oded and Matt Ball...)

But since that's a string column now, you should be able to use your Restrictions.Like approach in NHibernate as you mention.

Why is the `LIKE` operator not working with integer columns?

The id column is of type INTEGER, and therefore the value is being bound as such, as can be seen in your Query dump, it says 'type' => 'integer'. Being bound as an integer will cause it to be casted, and you'll end up with a comparison against 52 only.

You can workaround that by telling the query builder to treat the column as a string type. This can be done via the second argument ($types) of the query builders *where() methods:

$this->Invoices
    ->find()
    ->where(
        ['Invoices.id LIKE' => ($name . '%')],
        ['Invoices.id' => 'string']
    );

Performing a LIKE comparison on an INT field

You can CAST the field to a string:

 ... WHERE CAST(ProductID as CHAR) LIKE '%15%'

this is very bad for performance, as mySQL can't make use of any indexes it's created for the INT column. But then, LIKE is always slow, even when done on a varchar field: There's no way to have an index that speeds up a LIKE query.

It might be worth having a second varchar column that mirrors the int column's values and doing the LIKE on that one - you'd have to benchmark to find out whether it'll do any good.

Like Operator for Integer