I am looking at a table with phone1, phone2 and mobile. However the phone strings are not formatted properly.
The phone numbers are basically free text like this:
row1: phone1:19123123123 row2: mobile:+1 912 123 123 row3: phone2:1 912 123 123 row4: mobile:(+1) 912 123 123 row5: phone2:(+1)912-123-123
Is it possible to write a SQL query to find if one of the fields phone1, phone2 or mobile is +19123123123 but it needs to be able to match any one of those free text examples? Thanks
So in the above example, it should return all 5 records be those free text match that phone number and is in one of the 3 fields.
Assuming the only numbers and
+ in the column are in the phone number, you can use:
where regexp_replace(col, '[^+0-9]', '') = '+19123123123'
This removes everything that is not a
+ or digit.