Is there a test to see if a character field contains a number?

[bmarks]

In a stored procedure, we are converting a char field to a numeric.
Before conversion, I want to check to see if the field can be converted. Is there a way in Interbase to check this? Maybe something like the "is null" test?

 

[unclejimbob]

I guess there are several ways to do this based upon what sort of data you have - none
of the approaches is pretty.

For example if the field is alphanumeric and you expect non-numeric data within it
then you could use the InterBase WHEN 'error handling' construct:

WHEN {error [, error …] | ANY}
DO compound_statement

and handle the error thrown by the CAST

or you could have a look at the third party DLLs to see if you can find a isnumeric()-like
function - I can't find any, the best I could come up with is a substr() and then
iterate through the string looking for non-integers

or you could roll your own

a)

http://www.volny.cz/iprenosil/interbase/ip_ib_code_string.htm#_code_strpos

- use
the pos function and iterate through the string looking for integers

b)

http://www.experts-exchange.com/Database/Sybase/Q_20182624.html

(note that its
sybase code but looks easy enough to translate, you could issue something like
a select isNumber(<field>) into :ivar from into a local var and test the result)

create proc isNumber @value varchar(15)
as
begin
declare @number_count int
declare @decimal_count int
select @decimal_count = 0
select @number_count = char_length(@value)
while (@number_count > 0)
begin
if(substring(@value,@number_count,1)
IN ('0','1','2','3','4','5','6','7','8','9','.'))
begin
select @number_count = @number_count - 1
if(substring(@value,@number_count,1) = '.')
begin
select @decimal_count = @decimal_count + 1
if(@decimal_count > 1)

return -1
end
end
else return -1
end
return 0
end

I've just moved country so not really yet in a position to back up any of these
suggestions with code examples. Anyone else got some other ideas ? If so,
front them up. Also note I haven't touched InterBase for about 4 months now
(using MSSQL) so none of the above suggestions can be/have been validated.

cheers
ujb

 

[CHDH]

execuse me i have an idea which may be stupid!!
i think if there is no function to test if the field
is composed only from digits.
we can test it by something like this:
select fild,[,Field] from tablename
where testedfield not like '%a%A%b%B%..%z%Z'
i think that is not helpfull,but this is participation.
good luck

 

[bmarks]

Thanks for the responses. I think that the error handling seems like the best approach. We'll give it a try.

 

[unclejimbob]

CHDH - hello, yes, this is a tricky one - wish that InterBase had a regular expression parser built in as an SQL-extension to make our lives easier then it would be a doddle With SQL Server its a no-brainer using
isnumeric(), InterBase unfortunately does not have an equivalent. Ho hum.