Searching phone number in .NET Core-CodePudding

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?

CodePudding user response：

You can use SUBSTRING :

SELECT SUBSTRING([your column name], 3, 10) AS phone,   
   FROM [your table name]  
   WHERE [your column name] = [search term];

CodePudding user response：

First you have to create a computed persistant column with the reversed phone number like this :

ALTER TABLE T_PHONE ADD _PHONE_NUMBER_REVERSE AS REVERSE(PHONE_NUMBER) PERSISTED;

Second you have to create an index for performances searches :

CREATE INDEX X_001 ON T_PHONE (_PHONE_NUMBER_REVERSE);

Last, you have to use a WHERE clause like this one :

WHERE _PHONE_NUMBER_REVERSE LIKE REVERSE('03007418819')   '%'

This is the most efficient way to do that !

CodePudding user response：

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.