社会保险号的 T-SQL 正则表达式 (SQL Server 2008 R2) [英] T-SQL Regex for social security number (SQL Server 2008 R2)

问题描述

我需要在 SQL Server 2008 数据库表的 varchar 字段中查找无效的社会保险号.(有效 SSN 的定义格式为 ###-##-#### - 数字是什么无关紧要，只要它们在3 位数字"中破折号 2 位数破折号 4 位数"模式.

I need to find invalid social security numbers in a varchar field in a SQL Server 2008 database table. (Valid SSNs are being defined by being in the format ###-##-#### - doesn't matter what the numbers are, as long as they are in that "3-digit dash 2-digit dash 4-digit" pattern.

我确实有一个可用的正则表达式:

I do have a working regex:

SELECT * 
FROM mytable
WHERE ssn NOT LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'

这确实在列中找到了无效的 SSN，但我知道(好吧 - 我很确定)有一种方法可以缩短它以表明前一个模式可以有 x 次迭代.

That does find the invalid SSNs in the column, but I know (okay - I'm pretty sure) that there is a way to shorten that to indicate that the previous pattern can have x iterations.

我认为这会奏效:

'[0-9]{3}-[0-9]{2}-[0-9]{4}'

但它没有.

选择中是否有比上面更短的正则表达式?或者也许有，但 T-SQL/SQL Server 2008 不支持它！?

Is there a shorter regex than the one above in the select, or not? Or perhaps there is, but T-SQL/SQL Server 2008 doesn't support it!?

推荐答案

如果您打算获得更短的 LIKE 表达式变体，那么答案是否定的.

If you plan to get a shorter variant of your LIKE expression, then the answer is no.

在T-SQL中，您只能使用以下通配符在模式中:

In T-SQL, you can only use the following wildcards in the pattern:

%
- 任何零个或多个字符的字符串.WHERE title LIKE '%computer%' 查找书名中任意位置带有 computer 字样的所有书名.

%
- Any string of zero or more characters. WHERE title LIKE '%computer%' finds all book titles with the word computer anywhere in the book title.

_(下划线)
任何单个字符.WHERE au_fname LIKE '_ean' 查找所有以 ean 结尾的四字母名字(Dean、Sean 等).
[ ]
指定范围 ([a-f]) 或集合 ([abcdef]) 内的任何单个字符.WHERE au_lname LIKE '[CP]arsen' 查找以 arsen 结尾并以 C 和 P 之间的任何单个字符开头的作者姓氏，例如Carsen、Larsen、Karsen等.在范围搜索中，范围内包含的字符可能会根据排序规则的排序规则而有所不同.
[^]
任何不在指定范围 ([^a-f]) 或集合 ([^abcdef]) 内的单个字符.

_ (underscore)
Any single character. WHERE au_fname LIKE '_ean' finds all four-letter first names that end with ean (Dean, Sean, and so on).
[ ]
Any single character within the specified range ([a-f]) or set ([abcdef]). WHERE au_lname LIKE '[C-P]arsen' finds author last names ending with arsen and starting with any single character between C and P, for example Carsen, Larsen, Karsen, and so on. In range searches, the characters included in the range may vary depending on the sorting rules of the collation.
[^]
Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

因此，您的 LIKE 语句已经是可能的最短表达式.不能使用限制量词(像 {min,max} 这样的)，而不是像 \d 这样的速记类.

So, your LIKE statement is already the shortest possible expression. No limiting quantifiers can be used (those like {min,max}), not shorthand classes like \d.

如果您使用的是 MySQL，您可以使用一组更丰富的正则表达式实用程序，但事实并非如此.

If you were using MySQL, you could use a richer set of regex utilities, but it is not the case.

这篇关于社会保险号的 T-SQL 正则表达式 (SQL Server 2008 R2)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！