is varchar numeric
Home > IT Answers > Development > is varchar numeric
DuaneD
5 pts.
0
Q:
is varchar numeric
VARCHAR, SQL
sql to check varchar as numeric
ASKED: Jul 9 2009  8:25 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Kccrosser
1850 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Use the IS_NUMERIC() function.


[kccrosser] A more generic solution is to simply try to assign the value to an integer inside a Try..Catch block. E.g.:

-- SQL Server version
declare @i integer
declare @vc varchar(255)
...
begin try
set @i = @vc
end try
begin catch
-- if we get here, the value in @vc isn't a valid integer
end catch


-- Oracle PL/SQL version
declare i int
declare vc varchar2(255)
...
begin
i := vc;
exception
when VALUE_ERROR then -- sqlcode = -6502
-- handle bad numeric error
end;

If you want to handle floating point/decimal values, just make sure the receiving variable is of the correct data type.
Last Answered: Jul 31 2009  7:23 PM GMT by Kccrosser   1850 pts.
Latest Contributors: Mrdenny   46765 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Meandyou   1840 pts.  |   Jul 30 2009  1:37PM GMT

I did not recall the “IS_NUMERIC” function. To my knowledge this must be outside the ANSI standards.

By definition, in an RDBMS, if one wants numbers then one should use a numeric data type such as INTEGER, DECIMAL, FLOAT or whatever is appropriate for your data and within the confines of your particular RDBMS. A VARCHAR column, again by definition, can contain any characters.

One old approach to your problem that I recall involved a CASE statement and substringing thru the column one character at a time …
,CASE
WHEN ONE_CHAR BETWEEN ‘0′ AND ‘9′ THEN ‘NUMBER’

But If it is important to know if a CHARACTER column contains NUMERICS, then I would contend that something is wrong with the design of your system or application

 

Carlosdl   29805 pts.  |   Jul 30 2009  3:22PM GMT

You are right Meandyou, it is not a standard function. I think it is a t-sql function, but actually the function is ISNUMERIC() not IS_NUMERIC().

Depending on the RDBMS, there could be different ways to do it.

In Oracle, for example, one option could be performing a to_number() conversion, and catching the specific error (ORA-06502) that should be thrown if the argument wasn’t numeric.

 
0