CODEWITHSUNDEEP
sql-servert-sql
Bob Palmer
Bob Palmer

Reputation: 4762

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.

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).

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?

For example:

"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

Thanks in advance!

Upvotes: 17

Views: 93011

Answers (5)

StuartLC
StuartLC

Reputation: 107247

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]%';

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

Another MSDN Reference.

Upvotes: 38

Kanagaraj Retinamony
Kanagaraj Retinamony

Reputation: 31

This Query to list out Tables created with numeric Characters

select * from SYSOBJECTS where xtype='u' and  name  like '%[0-9]%'

Upvotes: 3

Abhi
Abhi

Reputation: 6568

This worked for me .

select total_employee_count from company_table where total_employee_count  like  '%[^0-9]%'

This returned all rows that contains non numeric values including 2-3 ..

Upvotes: 3

DOK
DOK

Reputation: 32841

I like the simple regex approach, but for the sake of discussion will mention this alternative which uses PATINDEX.

SELECT InvoiceNumber from Invoices WHERE PATINDEX('%[0-9]%', InvoiceNumber) = 0

Upvotes: 8

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

select * from table where column not like '%[0-9]%'

This query returns you all rows from table where column does not contain any of the digits from 0 to 9.

Upvotes: 9

Related Questions