There are strings in table
+1-123-456-7890
11234567890
+1 1234567890
1777555999
I have variable $phoneNumber. I want to pass variable in sql query to get matching records.
example:
$phoneNumber holds the value 1234567890
and using same variable I want to fetch following records in one query
+1-123-456-7890
11234567890
+1 1234567890
I want to select both records in one query. how can I match data (11234567890) with above saved numbers?
We can do this following the replacement logic already suggested here combined with regular expressions, to match only the phone numbers with the format you want to target:
WITH yourTable AS (
SELECT '+1-123-456-7890' AS phone UNION ALL
SELECT '+1 1234567890' UNION ALL
SELECT '+6512345678'
)
SELECT
phone,
REPLACE(REPLACE(REPLACE(phone, ' ', ''), '+', ''), '-', '') AS common_phone
FROM yourTable
WHERE phone REGEXP '\\+[0-9][[:space:]-][0-9]{3}-?[0-9]{3}-?[0-9]{4}';
select phone from myTable where phone like '{some regex pattern which can filter with the value of variable $phoneNumber}'; Here $phoneNumber can be any pattern with '+' and '-' or just number.
Try this !
Regex :
[\+]?[0-9](?:[-| ])?\d{3}[-]?\d{3}[-]?\d{4}
Verify through regxe101:
If both $phonenumber and your string column could (potentially) both have the troublesome characters, this is hard to solve with regular expressions.
where replace(replace(replace($phonenumber, ' ', ''), '-', ''), '+') = replace(replace(replace(phonecol, ' ', ''), '-', ''), '+')
$phoneNumber and compared it with the string result of the replace. SELECT phone FROM myTable where REPLACE(REPLACE(REPLACE(phone, ' ', ''), '+', ''), '-', '') = $phoneNumber;I solved this with using replace() in query.
I removed special characters from variable $phoneNumber (not through sql but before passing varable to sql) and compared it with the string result of the replace. SELECT phone FROM myTable where REPLACE(REPLACE(REPLACE(phone, ' ', ''), '+', ''), '-', '') = $phoneNumber;
Rethink the overall design.
Cleanse phone numbers before putting them into the table. Then that SELECT, and the other SELECTs that are yet to be written, will be simple.
REPLACE(REPLACE(REPLACE(telephone, ' ', ''), '+', ''), '-', '') LIKE $telephone+,-, and spaces, only the middle number is an exact match.$phoneNumbervalue is any of the following +1-123-456-7890 11234567890 +1 1234567890 all number should be treated as same because they have same number if ' ', '-', '+' characters not considered.