I am creating a function that checks a number of text columns until it finds a number and stops but the isnumeric condition is not working as expected. I debugged this in VS and the problem is shown in the picture. The variable @result is evaluating properly to 36 but the isnumeric is evaluating to 0 rather than 1 which causes the next IF Statement to be evaluated which should not happen. Why would the @variabletest variable evaluate to 0 when the value for @result is 36? (Set @variabletest = isnumeric(@result)? Any help would be appreciated
What is the length of @result
Currently nvarchar(5). The debug indicated it is evaluating as 36 which is correct. When I try to evaluate isnumeric,(@result), it comes back as 0 and the function enters the wrong If Statement. I must be missing something very basic as it is such a simple function.
If that string has any embedded non-printable characters - it is not going to be evaluated as a numeric value. Further - the ISNUMERIC function may not be the appropriate function to use, since it can and will evaluate some values as being numeric that you wouldn't expect.
For example: 1e1 evaluates as numeric as well as 1d1 - and other variations
1 Like
Not data type. I mean the actual length. It could have padding on either side
' 36'
' 36"
Etc or some other hidden string
You need to use the DATALENGTH function and not the LEN function. If the leading character(s) are spaces it will still evaluate to a numeric using that function.
I suspect it is a non-printable character.
1 Like
Copy paste to notepad++ and that tool usually can tell you whats up
Thanks for the responses as it helped pin this down. Notepad++ showed there was a trailing space in the result (3 characters) which was a surprise as the original query should have trimmed leading and trailing spaces. However, I tested an isnumeric with a trailing space and it still evaluated as 1 which is what I expected to happen. I even applied a trim function to the @result before evaluating it with isnumeric ( isnumeric(trim@result)) and the isnumeric function still evaluated to 0. When I applied a substring to it using only the first two characters, the isnumeric evaluated the condition properly. I'm still not clear why the isnumeric function didn't work with the trailing space or why the trim function didn't work as that would have made this easier. Since the length of numbers will vary from 1-4, I created a function to only keep the numerical values in the string and that seems to satisfy the requirements. Thanks for pointing me in the right direction
Almost certainly the reason the trim function didn't work is because that trailing character wasn't actually a normal space character. You could validate it by showing all symbols in Notepad++, which might lead you to a different solution using TRIM.
1 Like