August 9, 2002 at 11:57 am
Is there way to tell if a varchar field contains only numeric data. I'm doing data conversion varchar to int. The varchar field is supposed to contain only years '1999' and they need to be converted but sometimes the field contains 'All' or other non-numeric characters. thanks for any help
cheers
Randy
August 9, 2002 at 12:32 pm
You could pattern match for any alpha char and punctuation. Not 100% coverage, but close. What do you do when you can't convert it?
Andy
August 9, 2002 at 12:48 pm
Wouldn't the ISNUMERIC() system function suffice? Like so ...
IF ISNUMERIC(varchar_field) <> 0
BEGIN
do your tasks for numerics
END
ELSE
BEGIN
do your tasks for non-numeric field data
END
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
August 9, 2002 at 1:21 pm
thanks for the help, the isnumeric function
works fine.
cheers
Randy
August 9, 2002 at 3:06 pm
This function might come in handy in conjunction with isdate(). IsDate(1999) will return 1. With the NumericOnly function, you can say something like:
case
when isdate(dbo.numericonly(myfield)) = 0 then null else dbo.numericonly(myfield)
end
CREATE function NumericOnly(@string varchar(2000)) returns varchar(2000) as
Begin
/*
declare @string varchar(10)
set @string = '123abc44'
*/
declare @x int
set @x = 1
declare @out varchar(2000)
set @out = ''
while @x < len(@string) + 1
begin
if isnumeric(substring(@string, @x, 1)) = 1
set @out = @out + substring(@string, @x, 1)
set @x = @x + 1
end
return @out
end
August 9, 2002 at 3:20 pm
Better answer than mine!
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply