I’m working on a project where I have to add the functionality of searching for phone numbers using dot net core. on SignUp, we are storing phone numbers in SQL Server using the country code eg: "+923007418819".
So if the user searches the phone number with 03007418819
it’s not matching the data stored in the database and returns null.
The main thing I want is that if the user enters the phone number 03007418819
like this, it searches with the last 10 digits.
How can it be done?
3
Answers
You can use
SUBSTRING
:First you have to create a computed persistant column with the reversed phone number like this :
Second you have to create an index for performances searches :
Last, you have to use a WHERE clause like this one :
This is the most efficient way to do that !
Different approach:
the best way to solve this issue from its roots is to save all users’ phone numbers in the database in a specific format e.g.
00[countrycode][rest]
Create a helper that will format any valid phone number the user enters to the format you want when signing up. All phone numbers will be then formatted before being saved in the database. (The database will be then formatted and clean)
When the user tries to sign in, the same helper will first be called and format the number the user entered, and then you can easily search in the database with no magic. With this tiny modification, you can win the database index as well.