如何确定 SQL 中的 varchar 字段是否包含任何数字字符? [英] How would I determine if a varchar field in SQL contains any numeric characters?

问题描述

我正在做一个项目，我们必须弄清楚给定的字段可能是公司名称还是地址.

I'm working on a project where we have to figure out if a given field is potentially a company name versus an address.

在对其进行非常广泛的研究时，我们假设如果该字段不包含数字，则它很可能是名称与街道地址(我们的目标是 80% 的情况，知道一些必须手动完成).

In taking a very broad swipe at it, we are going under the assumption that if this field contains no numbers, odds are it is a name vs. a street address (we're aiming for the 80% case, knowing some will have to be done manually).

现在是手头的问题.给定一个表，为简单起见，只有一个 varchar(100) 列，我如何找到那些在字段中的任何位置都没有数字字符的记录?

So now to the question at hand. Given a table with, for the sake of simplicity, a single varchar(100) column, how could I find those records who have no numeric characters at any position within the field?

例如:

"Main Street, Suite 10A" --Do not return this.
"A++ Billing" --Should be returned
"XYZ Corporation" --Should be returned
"100 First Ave, Apt 20" --Should not be returned

提前致谢！

推荐答案

Sql Server 允许范围 [0-9] 或 Set [0123456789] 在 LIKE<中指定/code> 运算符，可与任何字符串通配符 (%) 一起使用.例如:

Sql Server allows for a regex-like syntax for range [0-9] or Set [0123456789] to be specified in a LIKE operator, which can be used with the any string wildcard (%). For example:

select * from Address where StreetAddress not like '%[0-9]%';

like 开头的通配符 % 显然会损害性能(可能是扫描)，但在您的情况下，这似乎是不可避免的.

The wildcard % at the start of the like will obviously hurt performance (Scans are likely), but in your case this seems inevitable.

另一个 MSDN 参考.

这篇关于如何确定 SQL 中的 varchar 字段是否包含任何数字字符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！