Converting varchar into an int just for the search query?

What I've been trying to do is to select a row from a table while treating the varchar cells as int ones,

Here's a little explanation: I have a table of phone numbers, some have "-" in them, some don't.

I wanted to select a number from the database, without including those "-" in the query. So I used this preg_replace function:

$number = preg_replace("/[^0-9]/","",$number); //that leaves only the numbers in the variable

and then I run the following query:

"SELECT * FROM `contacts` WHERE `phone` = '{$number}'"
开发者_StackOverflow社区

Now, of course it won't match sometimes since the number Im searching may have "-" in the database, so I tried to look for a solution, on solution is just converting the cells into int's, but I'm not interested in doing that,

So after looking around, I found a MySQL function named CAST, used like : CAST(phone AS UNSIGNED)

I tried to mess with it, but it didn't seem to work.

Edit:

I kept looking around for a solution, and eventually used MySQL's REPLACE function for that.

"SELECT * FROM `contacts` WHERE REPLACE(phone,'-','') = '{$number}'"

Thank you all for your help.

---

MySQL doesn’t support extraction of regex matches.

You could try writing a stored function to handle it, but your best bet is to convert the data to ints so that all the numbers are uniform. I know you said you don't want to do that, but if you can, then it’s the best thing to do. Otherwise, you could do something like:

"SELECT * FROM `contacts` WHERE `phone` = '{$number}' OR `phone` = '{$number_with_dashes}'"

That is, search for the plain number OR the number with dashes.

---

1. The easiest way to do it might be by using the REPLACE operator.

SELECT * FROM `contacts` WHERE REPLACE(REPLACE(`phone`, '-', ''), ' ', '') = '5550100';

What it does is simpy replacing all whitespaces and dashes with nothing, namely removing all spaces and dashes.

2. Another alternative to solve the problem would be to use LIKE. If the phone numbers with a dash always are formatted the same way like 555-0100 and 555-0199 you can simple insert a %-sign instead of the dash. If your number may be formatted in different ways you can insert a %-between every character. It's not a beautiful solution but it does the trick.

SELECT * FROM `contacts` WHERE `phone` LIKE '555%0100'; 

or

SELECT * FROM `contacts` WHERE `phone` LIKE '5%5%5%0%1%0%0';

3. You can use regular expressions. Since MySQL doesn't implement regex replace functions you need to use user defined functions. Have a look at https://launchpad.net/mysql-udf-regexp. It supports REGEXP_LIKE, REGEXP_SUBSTR, REGEXP_INSTR and REGEXP_REPLACE.

Edit: Removed my first answer and added some other alternatives.

---

I kept looking around for a solution, and eventually used MySQL's REPLACE function for that.

"SELECT * FROM `contacts` WHERE REPLACE(phone,'-','') = '{$number}'"