ASCII function returns NULL ??

Question

ASCII function returns NULL ??

dant12

SSCrazy

Points: 2712
More actions
August 31, 2010 at 9:49 am

#226430

i have a CLR UDF that validates email addresses but i just had a problem where i got the following error
System.ArgumentOutOfRangeException: Length cannot be less than zero.
Parameter name: length
System.ArgumentOutOfRangeException:
at System.String.InternalSubStringWithChecks(Int32 startIndex, Int32 length, Boolean fAlwaysCopy)
i know this aint a CLR forum, the problem is not in the CLR itself but rather in the data.
looking at the data that was being parsed i looked for hidden ascii characters where there should be normal blank spaces thats when i saw the NULL. i'm pretty sure thats where my problem lies and i'm looking for a way to parse this and avoid this error.
the weirdest of all, our old T-SQL UDF works like a charm, no errors at all, while the CLR UDF clearly does not like unexistant characters.
here is the screen with the select
[/URL]
--
Thiago Dantas
@DantHimself

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

HowardW One Orange Chip Points: 29109 More actions · Answer 1

Assuming these are varchar/nvarchar fields, then if there's no data in that substring range (e.g. the field length is shorter than 613 chars) then it will return a blank string which will return NULL to ASCII and UNICODE:

SELECT ASCII('')

Your function needs to be coded to be able to deal with NULL's, or you need to filter them out in the where clause before calling the function.

P.S. If those are real email addresses in the screenshot, please could you remove it? I'm sure people wouldn't appreciate their email addresses being posted on a forum...

dant12 SSCrazy Points: 2712 More actions · Answer 2

HowardW (8/31/2010)
Assuming these are varchar/nvarchar fields, then if there's no data in that substring range (e.g. the field length is shorter than 613 chars) then it will return a blank string which will return NULL to ASCII and UNICODE:
SELECT ASCII('')
Your function needs to be coded to be able to deal with NULL's, or you need to filter them out in the where clause before calling the function.
P.S. If those are real email addresses in the screenshot, please could you remove it? I'm sure people wouldn't appreciate their email addresses being posted on a forum...

the emails are bogus, i tested the CLR UDF passing '' and it worked, same with null.

what is most weird is that it breaks before entering the function (i put a breakpoint on the first line of code and it doesnt even get hit)

--
Thiago Dantas
@DantHimself