value with trailing numbers

[#1]

Currently Im having issues with this piece of code. The first and second lines on the if statement are outputting correctly ( '40050101' changing to '2012' and '40020201' to '2014').

But the last 4 are not.

Take the numcer '400401' for example. In the field @17 there are a number of values beginning with '400401'. For instance I have '40040100', '40040101', '40040102' and '40040103' - I think this is why it doesnt like the value '400401'

I've used the trim and rtrim functions to help but it still doesnt seem to work. Would anyone know how to overcome this issue? Thanks

if(trim(@17) = '40050101', '2012',

if(trim(@17) = '40020201', '2014',

if(trim(@17) = '400401', '2011',

if(trim(@17) = '4005', '2009',

if(trim(@17) = '4006', '2040',

if(trim(@17) = '40010100', '2001')))))) as bdg_link,

[swuehl · Accepted solution]

Maybe try the like operator together with a wildcard:

...

if( trim(@17) like '400401*', '2011'),

...

Or try using left() function

if( left(trim(@17),6) = '400401', '2011'),

The trim function removes leading and trailing spaces only, so you don't need it if there are no spaces.

hope this helps,

Stefan

[#3]

Try using Left function

if(left(@17,8) = '40050101', '2012',

if(left(@17,8) = '40020201', '2014',

if(left(@17,6) = '400401', '2011',

if(left(@17,4) = '4005', '2009',

if(left(@17,4) = '4006', '2040',

if(left(@17,8) = '40010100', '2001')))))) as bdg_link,

Edit: And once again I type to slow haha. See Above that should work