比较用户输入的字符串与数据库列 [英] Comparing string entered by user with a DB column
问题描述
您好:
我遇到一个问题，我必须将字符串列与用户插入的任何值进行比较.
列中的值可以采用以下任何格式；和字符串不能超过19个字符
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
问题在于我们拥有的数据包含如此多的格式，并且在sqlserver中进行比较时我们无法处理所有数据，因为我在查询时不能在where子句中使用任何标量函数有一定的局限性，因为它会干扰索引等性能. >
我唯一的选择是通过C#代码处理它.
有谁能帮助我并指导我在C#中创建一个通用函数，该函数可以处理上述所有情况以及其他可能的情况
Nazish Abbasi
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
推荐答案
我不会这样做.
相反，我会将您的数据库数据转换为统一格式:并在以后的所有输入中强制执行该数据，并根据需要进行转换.如果您的数据库中有多种格式，则需要在进行任何比较(讨厌)之前在存储过程中对它们进行规范化，或者在客户端应用程序中全部检索并进行比较(如果数据库是大小)
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)
您俩都是正确的，但问题是我们根本没有限制用户.当我们需要处理同一列中所有国家/地区的电话号码时，就会发生实际问题.每个公司都有自己的格式，每个用户都希望返回并以与他/她输入的格式相同的方式查看该号码.
无需根据知识产权来确定国家，也无需根据知识产权的基础来改变掩码.
是的，我们已经在计划实施一种HIDensity之类的解决方案.但这会花费很多时间，因此我们必须在进行任何新的解决方案之前对其进行搜索.
所以我需要一些通用的字符串，如果您可以，这可能对我有帮助.
最好的问候；
Nazish Noor
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
然后您应该从电话号码中删除所有特殊字符
Then you should remove all the special characters from the phone number
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.
这篇关于比较用户输入的字符串与数据库列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
