June 30, 2008 at 2:29 pm
Is something like this even possible? If my field is longer than 3 (varchar field), I want to use the first two positions of the SERVCODE to insert into a field on my table. Of course this is the only piece of the code I need assistance with and I greatly appreciate any, and all, responses.
CASE SERVCODE when len(SERVCODE) > 3 then substring(SERVCODE, 1, 2) else null AS RevenueCode,
-- You can't be late until you show up.
June 30, 2008 at 2:39 pm
if the string is also 3+ char why not use the left function as well?
I think the correct syntax for the case statement in this option is
CASE
WHEN len(servcode) > 2 then left(servcode,2)
WHEN len(servcode) < 2 then null
else servcode
END as revenuecode,
my reasoning for the change of code is
the first option when the code is > 2 long and left is simplest function
the second option - its only null when < 2 length so wont be a valid 2 length code
the third option it is either null or is 2 long so leave as is
Maybe others can comment on wheter the substring or left function is the most efficient
June 30, 2008 at 2:52 pm
Sweet, that worked. After beating my head against the wall for a couple of hours, I figured I'd post and hope for a quick response (I have a tendency to sometimes over-complicate the matter at hand). One of my colleagues uses left (and right), I've always used substring....Thanks for the help!
-- You can't be late until you show up.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply