Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
ltrim(replace(Individual_Item_Number,'77','')) as Dupe I used this to try and identify individual_items where they start with '77%' and return it's non 77 equivalent: Where ind_item = 12345, Dupe is 12345 as it should be. Where ind_item = 7712345, Dupe is 12345 as it should be...however, if ind_item = 1277345, it returns 12345 when I would like it to remain 1277345. I only want it to act if the two left most digits meet the '77' starting criteria. Thoughts?
Hi,
Try with this
if(Left(Trim(Individual_Item_Number),2)='77',Right(Trim(Individual_Item_Number),len(Trim(Individual_Item_Number))-2,Trim(Individual_Item_Number)) as Dupe
Hope it helps
Celambarasan
I think you'd better to use the below.
If(WildMatch(Ltrim(Individual_Item_Number), '77*') > 0
, ltrim(replace(Individual_Item_Number,'77',''))
, Ltrim(Individual_Item_Number)
) as Dupe
Good luck!
Steve Kang.
Not quite, still strips all instances of 77. 7717708 returns 108 as opposed to 17708.
Hi,
Try with this
if(Left(Trim(Individual_Item_Number),2)='77',Right(Trim(Individual_Item_Number),len(Trim(Individual_Item_Number))-2,Trim(Individual_Item_Number)) as Dupe
Hope it helps
Celambarasan
I hope it achieves what u want.
If(WildMatch(Ltrim(Individual_Item_Number), '77*') > 0
,mid(Individual_Item_Number,3),Individual_Item_Number
) as Dupe,
Goodluck
Hi,
This works as you expect, in this we are checking the two numbers from left if it is 77 then we truncate the left 77 and fetch the remaining numbers by using Right().
=If(Left(Trim(Individual_Item_Number), 2) = '77',
Right(Trim(Individual_Item_Number), len(Trim(Individual_Item_Number))-2),
Trim(Individual_Item_Number)) as Dupe
Hope this helps you.
Regards,
Jagan.
Thank you all very much. JL