Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have a field which I require for mapping purpose. But it is adding G00/G0/G prefix to all the order numbers that I want to match.
For Example : G0080202594 or G0285144563 or G1085346991
So, what I want is to trim off the initial G or any 0 afterwards which will give me something like this 80202594/285144563 /1085346991.
As I can't tell for sure how many 0s will be there after G letter so I can't just remove a specific number of letters from beginning. I would like to know how can I remove the G and any 0 that comes right after(if any).
Any help will be appreciated.Thanks in advance.
Pranav
One solution could be
Replace(LTrim(Replace(Mid([Order Number], 2, Len([Order Number])-1), '0', ' ')), ' ', '0')
Below should cover your scenarios mentioned in your post. Just remove G and convert to a number
Num(Replace('G0080202594','G',''))
Hope it works out
or Maybe like this :
Num(mid('G0080202594',2,len('G0080202594')))
Hi Pranav,
Try this:
PurgeChar(text, remove_chars)
Example Result:
PurgeChar ( 'a1b2c3','123' ) Returns 'abc'
PurgeChar ( 'a1b2c3','312' ) Returns 'abc'
In your Case:
PurgeChar ( 'G0080202594','G0')
It will result you --> 822594
Note -- It will remove all occurrence of G and 0.
I hope, it will help you.
Hi Amit,
Purging is not the solution. It will remove the Valid "0" from the Fieldvalue. i.e. 80202594 -- > 822594. this is not what he want. He just want to remove the Leading "0" and the characther "G"
Hi Jayant,
I think in that case, We can directly use
Num(PurgeChar ('G0080202594','G') ) instead of PurgeChar ( 'G0080202594','G0')
It will remove G and Num will removes all initial 0's, as Zero''s before any non zero numbers automatically will be removed, when we use Num().
I think, it will work .
One solution could be
Replace(LTrim(Replace(Mid([Order Number], 2, Len([Order Number])-1), '0', ' ')), ' ', '0')
May be another way can be :
Write this as an expr or in load script.
= Mid(TextStr, FindOneOf(TextStr, '123456789'), Len(TextStr)) AS NewStr
Hi Sunny,
Thanks for the solution. Works for my current requirement but if in future I get order numbers without G then this expression will trim off the first digit as well. So i think i will have to put a condition to check if first character is number or a letter and then do the needful. But this solution should do it for now. Again thank you so much for the help.
Pranav
Thanks everyone for the suggestions. I appreciate it.