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?
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
(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.
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
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.