6 Replies Latest reply: Feb 29, 2012 9:02 AM by Jonathan Love

Remove leading characters IF

Jonathan Love Feb 28, 2012 7:03 PM

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?

Remove leading characters IF

Steve Kang Feb 28, 2012 8:58 PM (in response to Jonathan Love)

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.
Like Show 0 Likes (0)

Actions
- Remove leading characters IF
  
  Jonathan Love Feb 28, 2012 9:08 PM (in response to Steve Kang )
  
  Not quite, still strips all instances of 77. 7717708 returns 108 as opposed to 17708.
  
  Like Show 0 Likes (0)
  
  Actions
  - Re: Remove leading characters IF
    
    Celambarasan Adhimulam Feb 28, 2012 11:59 PM (in response to Jonathan Love)
    
    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
    
    Like Show 1 Like (1)
    
    Actions
    - Remove leading characters IF
      
      Jonathan Love Feb 29, 2012 9:02 AM (in response to Celambarasan Adhimulam )
      
      Thank you all very much. JL
      
      Like Show 0 Likes (0)
      
      Actions
  - Remove leading characters IF
    
    jagan mohan rao appala Feb 29, 2012 3:08 AM (in response to Jonathan Love)
    
    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.
    
    Like Show 0 Likes (0)
    
    Actions
Re: Remove leading characters IF

Chee Tiong Chua Feb 29, 2012 2:38 AM (in response to Jonathan Love)

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
Like Show 0 Likes (0)

Actions

Go to original post