Count the number of characters

[peewan]

Hi,
i have a field:
[Value] contains multiple value in it.
If 4 digits long, will be I_Code.
If 6 digits long will be MemberID.
If 12 digits long will be Contact ID.

So I want to count the length of [Value], then use "case...when" to get what i want.

The problems is when I use Length ([Value]), characters_length ([Value]) and it did not work, give me error -9.

Please help. Thanks!

[adam_mc]

To use the Length function, I think you have to cast it to a varchar() first.

But, how about this instead so that you can leave it as a number.

case
when [Value] <= 9999 then 'I_Code'
when [Value] <= 999999 then 'MemberID'
when [Value] <= 999999999999 then 'ContactID'
end

This assumes that you only have 4, 6, and 12 digit values.

Hope this helps,
Adam.