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:
First, using
CAST(bkID AS NVARCHAR(MAX))
will affect the performance of the query, because it will not use any index, also casting toNVARCHAR(MAX)
will decrease the performance.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 theasp
tag not in the database, so they are used in the application level not the data level.if you are using
CAST(bkID AS NVARCHAR(MAX))
to search for thebkid
column that contains a specific digit (ex: search for1
-> result1
,10
,11
,...), then try Casting to a specific size (ex:CAST(bkID as NVARCHAR(10)
)It is recommended to use parameterized queries for a better performance and to prevent Sql injection attacks. look at @un-lucky answer
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
Difference Between Filtering Queries in Join and Where
How to Execute a Text File from SQL Query
Suggestions for Implementing Audit Tables in SQL Server
How to Use a MySQL Database with an App Engine Application
Selecting Column Names That Have Specified Value
Check If Table Exists and If It Doesn't Exist, Create It in SQL Server 2008
Get Multiple Values in SQL Server Cursor
How to Ignore "Duplicate Key" Error in T-SQL (SQL Server)
SQL Error: Misuse of Aggregate
Copy Data from One Column to Other Column (Which Is in a Different Table)
SQL Query for Getting Data for Last 3 Months
How to Insert Arabic Characters into SQL Database
MySQL Full Text Search with Partial Words
Converting a String to Hex in SQL
SQL Server Query Time Out Depending on Where Clause