Use LIKE '%' with integer in SQL

Question

I have this procedure in SQl Server:

create procedure AutoSearchTeamByMobile
@Team_Mobile int
As
select * from Team_Info where Team_Mobile like @Team_Mobile+'%';

And I have this code in C#:

private void textBoxX4_TextChanged(object sender, EventArgs e)
    {
        int tMobile= int.Parse(textBoxX4.Text);
        SqlCommand com = new SqlCommand("AutoSearchTeamByMobile", con);
        com.Parameters.Add("@Team_Mobile", SqlDbType.Int).Value = tMobile;
        com.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();
        da.SelectCommand = com;
        da.Fill(ds, "Team_Info");
        dataGridViewX1.DataSource = ds.Tables["Team_Info"];
    }

I have a list of mobile numbers and they are saved in column called Team_Mobile with data type as int, one of them start with'711......' another one start with '700......', so now when I write in textBoxX4 only '7' then all numbers(which start with 711 or 700) should be appears in dataGridViewX1, but when I write '71' then only numbers which start with '711' could be appears in dataGridViewX1. When I write '70' then only numbers which start with 700 should be appears in dataGridViewX1. I did not get what I need, actually I did not see any thing same as I did not do any thing.

My needs, when I write 71 then all records that have 711 in Team_Mobile column should be still appear and the other number should be hide.

But I did it with Team_Names and it works because the data type of Team_Names is nvarchar, but the Team_Mobile is int.

sql:

create procedure AutoSearchTeam
@Team_Name nvarchar (50)
As
select * from Team_Info where Team_Name like @Team_Name+'%';

C#:

    private void textBoxX1_TextChanged(object sender, EventArgs e)
    {
        string t_name = textBoxX1.Text;
        SqlCommand com = new SqlCommand("AutoSearchTeam", con);
        com.Parameters.Add("@Team_Name", SqlDbType.NVarChar, 50).Value = t_name;
        com.CommandType = CommandType.StoredProcedure;
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();
        da.SelectCommand = com;
        da.Fill(ds, "Team_Info");
        dataGridViewX1.DataSource = ds.Tables["Team_Info"];
    }

My question is: Is there anyway to replace % to use it with integer?

What can I replace % to use it with integer to get what I have explained above?

Sorry for my English language.

Solution 1

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.

Solution 2

For example this

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

Solution 3

Yes I believe you can either via:

SELECT * FROM MyTable WHERE Team_Mobile >= @Team_Mobile

Above return anything that is greater than 700 (as per your example) without modifying data types.

Or perhaps

SELECT * FROM MyTable WHERE CAST(Team_Mobile AS NVARCHAR(MAX)) LIKE @Team_Mobile + '%'

Casts the column to a string thus allowing you to use wildcards.