What would be the most efficient way to check for numbers in a field that would contain a few letters? I would like to do this if the where statement as possible.
The data would look something like this:
3833N4323 32N907654 5W5840904
-
Are you checking whether numbers exist in the field? Do you wish to extract those numbers?Austin Salonen– Austin Salonen2010-01-11 22:40:47 +00:00Commented Jan 11, 2010 at 22:40
-
Just checking to make sure theres numbers in the field and will then selecting whats in there in its entirety.Todd– Todd2010-01-11 22:59:26 +00:00Commented Jan 11, 2010 at 22:59
Add a comment
|
3 Answers
Checking for at least one number in a field (corrected):
WHERE PATINDEX('%[0-9]%', field) != 0
Checking for only numbers in a field:
WHERE TRY_CONVERT(field AS int) IS NOT NULL
4 Comments
Todd
how is this? AND PATINDEX('%[^0-9]%', pq.ReportNumber) > 1
Mark Byers
You have the REGEXP keyword on SQL Server? What version of SQL Server is that?
Todd
This seems to work. AND PATINDEX('%[0-9^]%', pq.ReportNumber) > 1
HelloW
The last block of code will not work. It needs to be changed to WHERE ISNUMERIC(field) = 1
A simple LIKE to find any number will suffice...
...WHERE LIKE '%[0-9]%'
Comments
select ISNUMERIC(data)
2 Comments
Pablo Camara
Unfortunately for me, this value: 33465,33465 , returns 1 ( number with a comma, which in my case I consider a string, not the kind of numbers I want to allow )
Steven Kuypers
In 'my' Microsoft SQL Server 2019 version all of these return 1: select ISNUMERIC('$'), ISNUMERIC(','), ISNUMERIC('-'). Also select ISNUMERIC('123123123E1') -notice the E near the end- returns 1. However, ISNUMERIC('123123123F1') -now with an F- returns 0. It seems you cannot really rely on this function.