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
Asked
Active
Viewed 3.6k times
8
-
Are you checking whether numbers exist in the field? Do you wish to extract those numbers? – Austin Salonen Jan 11 '10 at 22:40
-
Just checking to make sure theres numbers in the field and will then selecting whats in there in its entirety. – Todd Jan 11 '10 at 22:59
3 Answers
21
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
Charlieface
- 52,284
- 6
- 19
- 43
tanerkay
- 3,819
- 1
- 19
- 28
-
-
You have the REGEXP keyword on SQL Server? What version of SQL Server is that? – Mark Byers Jan 11 '10 at 22:57
-
-
2The last block of code will not work. It needs to be changed to WHERE ISNUMERIC(field) = 1 – HelloW Sep 13 '13 at 13:41
2
select ISNUMERIC(data)
Gabriel McAdams
- 56,921
- 12
- 61
- 77
-
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 ) – Pablo Camara Apr 14 '20 at 20:47
-
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. – Steven Kuypers Feb 09 '23 at 23:08