SQL Like Condition to Check for Integer

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;

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

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.

Postgre SQL LIKE for Integer

Try doing:

cast(size as text)

It should help.

Like query on Integer primary key Id

You can use fragment to create a query identical to what you want:

query =
from q in CustomerModel,
where: like(fragment("CAST(? AS TEXT)", q.id), "1%")

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

Searching an int column on the basis of a string value

Some recommendations

The query you have provided need to be optimized:

  1. First, using CAST(bkID AS NVARCHAR(MAX)) will affect the performance of the query, because it will not use any index, also casting to NVARCHAR(MAX) will decrease the performance.

  2. bkStatus is a numeric column so you have to use = operator and compare with numeric values (0 or 1 or ...), also the text values provided are defined in the asp tag not in the database, so they are used in the application level not the data level.

  3. if you are using CAST(bkID AS NVARCHAR(MAX)) to search for the bkid column that contains a specific digit (ex: search for 1 -> result 1,10,11,...), then try Casting to a specific size (ex: CAST(bkID as NVARCHAR(10))

  4. It is recommended to use parameterized queries for a better performance and to prevent Sql injection attacks. look at @un-lucky answer

  5. You can use a dictionary Object to store the ID values related to the keywords

Example

Note: The use of CAST and Like will not used any index, this example is based on your requirements (i tried to combine the recommendations i provided with others recommendations)

var dicStatus = new Dictionary<int, string> { 
{ 0, "Pending" },
{ 1, "Booked" },
{ 2, "Cancelled" }
// ...
};

string querySql = " SELECT * FROM View_Booking" +
" WHERE CAST(bkID AS NVARCHAR(10)) LIKE @bkID" +
" OR bkSlot LIKE @bkSlot" +
" OR bkStatus = @status";
using (SqlConnection dbConn = new SqlConnection(connectionString))
{
dbConn.Open();
using (SqlCommand sqlCommand = new SqlCommand(querySql, dbConn))
{
sqlCommand.Parameters.Add("@bkID", SqlDbType.VarChar).value ="%" + keyword + "%";
sqlCommand.Parameters.Add("@bkSlot", SqlDbType.VarChar).value ="%" + keyword + "%";
sqlCommand.Parameters.Add("@status", SqlDbType.Int).value = dicStatus.FirstOrDefault(x => x.Value == keyword).Key;
sqlCommand.ExecuteNonQuery();
}
}

Also if BkID is an integer column it is better to use

sqlCommand.Parameters.Add("@bkID", SqlDbType.Int).value = (Int)keyword ;

References & Helpful Links

  • Like operator for integer
  • Performance hit using CAST in T-SQL
  • How much do CAST statements affect performance?
  • SQL Server: Index columns used in like?
  • C# Dictionary get item by index
  • Getting query to work with parameter and "like"


Related Topics



Leave a reply



Submit