January 10, 2012 at 7:36 pm
I have a function which accepts a varchar input and returns a bit value. It fails when CR/LF char(13) and char(10) and its variations is passed as the input string. The error I am getting is in the case statement in the if loop. The error says: Error converting varchar to numeric data type.
CREATE function [cleanse].[Honda](
@String varchar(max)
) returns Bit
as
begin
declare @decimal decimal(38,3) = 0.0;
set @String = rtrim(ltrim(@String));
if (isnumeric(@String) = 1 and (len(@String) < 35)) begin
set @decimal = cast (@String as decimal(38,3));
end
return
case
when (@String is null) then 0
when (@String = '') then 0
when (@String in ('F','False','N','No','0')) then 0
when (isnumeric(@String) = 1 and @decimal = 0) then 0
else 1
end
end
Here are a few test cases this function is to be tested for:
select cleanse.Honda(char(13)+char(10))as testLineFeed; -- valid (carriage return/line feed ) 0
declare @string varchar(8000) = char(13)+char(10) + 'tom';;
select cleanse.Honda(@string)as testLineFeed; -- valid (carriage return/line feed ) 0
declare @string2 varchar(8000) = char(13)+char(10) + '0';;
select cleanse.Honda(@string2)as testLineFeed; -- valid (carriage return/line feed ) 0
declare @string3 varchar(8000) = '0' + char(13)+char(10);
select cleanse.Honda(@string3)as testLineFeed; -- valid (carriage return/line feed ) 0
declare @string4 varchar(8000) = '0' + char(13)+char(10) + '0';
select cleanse.Honda(@string4)as testLineFeed; -- valid (carriage return/line feed ) 0
January 11, 2012 at 3:38 am
I guess the whole problem is the ISNUMERIC function. It returns 1 for \r and :
SELECT ISNUMERIC(char(10)), ISNUMERIC(char(13))
See this article by Jeff Moden for alternatives:
http://www.sqlservercentral.com/articles/IsNumeric/71512/
Hope this helps
Gianluca
-- Gianluca Sartori
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply