I am working in SQL Server 2008. I am trying to test whether a string (varchar) has only digit characters (0-9). I know that the IS_NUMERIC function can give spurious results. (My data can possibly have $ signs, which should not pass the test.) So, I'm avoiding that function.
I already have a test to see if a string has any non-digit characters, i.e.,
some_column LIKE '%[^0123456789]%'
I would think that the only-digits test would be something similar, but I'm drawing a blank. Any ideas?
This question is related to
sql
sql-server
sql-server-2008
Use Not Like
where some_column NOT LIKE '%[^0-9]%'
declare @str varchar(50)='50'--'asdarew345'
select 1 where @str NOT LIKE '%[^0-9]%'
I was attempting to find strings with numbers ONLY, no punctuation or anything else. I finally found an answer that would work here.
Using PATINDEX('%[^0-9]%', some_column) = 0 allowed me to filter out everything but actual number strings.
Solution:
where some_column NOT LIKE '%[^0-9]%'
Is correct.
Just one important note: Add validation for when the string column = ''
(empty string). This scenario will return that ''
is a valid number as well.
Method that will work. The way it is used above will not work.
declare @str varchar(50)='79136'
select
case
when @str LIKE replicate('[0-9]',LEN(@str)) then 1
else 0
end
declare @str2 varchar(50)='79D136'
select
case
when @str2 LIKE replicate('[0-9]',LEN(@str)) then 1
else 0
end
The selected answer does not work.
declare @str varchar(50)='79D136'
select 1 where @str NOT LIKE '%[^0-9]%'
I don't have a solution but know of this potential pitfall. The same goes if you substitute the letter 'D' for 'E' which is scientific notation.
There is a system function called ISNUMERIC for SQL 2008 and up. An example:
SELECT myCol
FROM mTable
WHERE ISNUMERIC(myCol)<> 1;
I did a couple of quick tests and also looked further into the docs:
ISNUMERIC returns 1 when the input expression evaluates to a valid numeric data type; otherwise it returns 0.
Which means it is fairly predictable for example
-9879210433
would pass but 987921-0433
does not.
$9879210433
would pass but 9879210$433
does not.
So using this information you can weed out based on the list of valid currency symbols and +
& -
characters.
DECLARE @x int=1
declare @exit bit=1
WHILE @x<=len('123c') AND @exit=1
BEGIN
IF ascii(SUBSTRING('123c',@x,1)) BETWEEN 48 AND 57
BEGIN
set @x=@x+1
END
ELSE
BEGIN
SET @exit=0
PRINT 'string is not all numeric -:('
END
END
Source: Stackoverflow.com