Comparing string entered by user with a DB column

Question

0.00/5 (No votes)

See more:

Hi:
I am having an issue that i have to compare a string column with any value inserted by a user.
Values in column can be in any of formats below; and string cannot be more than 19 charachters

00000000000
(000) 000-0000
000-000-0000
22222222222222
000 000 00000
000 000 0000
000-000-0000
000-000-00000
00 00 000 0000
000000000000
0000 0000 000

problem is that the data we have contains so many formats and we cannot handle all while comparison in sqlserver as I have some limitation while querying that I cant use any scallar function in where clause as it will disturb indexes and so as performance.

The only option i have is to handle it through C# code.

Can any one help me and guide me in making a generic function in C# which would handle all of the cases mentioned above and other which can be possible

Nazish Abbasi

Posted 7-May-12 0:09am

NazishAbbasi

Add a Solution

3 solutions

Solution 2

both of You are right but the problem is we are not restricting users at all. infact problem occurs when we need to handle phone numbers from all countries in same column. each company has its own format and every user want to get back and see that number in same format as he/she entered.

there is no need of deciding country on the basis of IP and changing mask on the pasis of IP.

Yes we are already planning to the implement a solution like the one HIDensity. but this would offcource take time and we have to search it before any new solution.

so i need some generic string which may help me if you ppl can.

Best Regards;
Nazish Noor

Posted 7-May-12 1:06am

NazishAbbasi

Solution 3

Then you should remove all the special characters from the phone number

C#

System.Text.RegularExpressions.Regex.Replace(phNumber, "\\D", "");

Then add another column in the table containing only number part of phone number and use that column for comparison.

Posted 7-May-12 1:30am

akee seth

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2012-05-07T00:28:00

Solution 1

I wouldn't do it.
I would instead convert your database data to a uniform format: and enforce that on all future inputs, converting as necessary. If you have a variety of formats in your DB then you need to either normalize them in a stored procedure before you do any comparison (nasty), or retrieve them all and compare them in your client app (even nastier if the database is of any size)

Posted 7-May-12 0:28am

OriginalGriff

Comments

HiDensity 7-May-12 6:40am

I absolutely agree with OriginalGriff.

If you cannot handle it within the database you would have to go through any record, converting the data and comparing it with the input string.
This aproach is absolutely not to handle and has nothing to do with performance.

My suggestion would be: Add a new column to your table, put normalized data into this column, create a stored procedure that normalizes the chaotic data and set a trigger to the table, which calles your procedure on insert or update.
Add an index to the new normalized column and then you can work with a simple where clause, which should be much much much faster than any C# solution can be.

OriginalGriff 7-May-12 7:22am

:thumbsup:
Deserves a five - I'll apply it as soon as I see an appropriate message!
-Update- Applied to your one answer: http://www.codeproject.com/Answers/379540/Mdichild-forms-for-billing#answer1

Monjurul Habib 8-May-12 16:29pm

5!