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!
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.