How to Use Like Condition in SQL with Numeric Field

sql like operator to get the numbers only

You can try this

ISNUMERIC (Transact-SQL)

ISNUMERIC returns 1 when the input
expression evaluates to a valid
numeric data type; otherwise it
returns 0.

DECLARE @Table TABLE(
        Col VARCHAR(50)
)

INSERT INTO @Table SELECT 'ABC' 
INSERT INTO @Table SELECT 'Italy' 
INSERT INTO @Table SELECT 'Apple' 
INSERT INTO @Table SELECT '234.62' 
INSERT INTO @Table SELECT '2:234:43:22' 
INSERT INTO @Table SELECT 'France' 
INSERT INTO @Table SELECT '6435.23'
INSERT INTO @Table SELECT '2' 
INSERT INTO @Table SELECT 'Lions'

SELECT  *
FROM    @Table
WHERE   ISNUMERIC(Col) = 1

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

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;

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

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.

Access SQL: like function on a number field

That sound a little fishy.. are you sure you can use that query? Don't know about Access but almost any other DBMS allows it.

If it really doesn't work, you can do this:

select * from table where STR(numberfield) like "*9"

EDIT: Maybe it didn't work because you used % which is used with * in Access :

select * from table where numberfield like "*9"

---

Related Topics