Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

bmarks

IS-IT--Management
Sep 25, 2001
85
CA
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

or you could roll your own

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

b) (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
 
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
 
Thanks for the responses. I think that the error handling seems like the best approach. We'll give it a try.
 
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. :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top