Removing particular characters

tyaskoa9090

Forums

Hi Brian,
This is cool resources. Thanks!
Do you have functions to remove certain words/characters.
Example: Address field to be removed any "ST." or "STREET" or "STR".
The characters that need to be removed can be located anywhere within an address.
Really need your help. Many thanks.
 

Brian Element Wed, 04/02/2014 - 07:16

Hello and welcome to the site, I am glad you are enjoying it.

In the equation editor there is the @remove function that allows you to remove certain functions.  In your case you would use @remove(ADDRESS_FIELD, "ST."), you would have to do it for each permatation or you can do something like this @remove(@remove(@remove(ADDRESS_FIELD, "ST."), "STREET", "STR").  If you wish to do this in a script you can access the function by changing the @ for an i, so the function would be iRemove.

Let me know if this helps.

tyaskoa9090 Thu, 04/03/2014 - 01:01

Thanks for your prompt response, Brian.
Actually, I already tried using @remove(Field name, "ST.") but it turned to remove first letter. Then, I tried to apply your function and it return in the same result.
Please advice. Many thanks.
 

Brian Element Thu, 04/03/2014 - 05:43

I am sorry to hear that, the problem might be that the @remove is case sensative.  So "ST" and "st" are viewed as being different.  Maybe try @remove(@upper(FIELD_NAME), "ST.").  If this doesn't work could you post some examples of what you want to remove and the contents of the field you want it removed from, I might see the problem then.

Good luck.

tyaskoa9090 Thu, 04/03/2014 - 23:38

All specific words are upper case.
Kindly refer to attached image.
First colomn is the source data.
Example : 
JL. CENDRAWASIH NO. 21 TANJUNGMAS S
JLN. URIP SUMOHARJO NO. 246  MACCIN
Second colomn is the result which I used  @remove(@remove(@remove(STREET, "JL."), "JLN."), "JALAN").
Please consider "JL." and "JLN."  are similar with "ST." or "STREET"
Again, Thank  you.
 

Brian Element Fri, 04/04/2014 - 07:21

Thanks for supplying the image, I figured out what was wrong and learned something also, I love that.  It turns out that the remove only seems to work on one character and not a group of characters.  So I tried this equation on your text and I think I obtained the result you are looking for.  Here is the equation and you can see from the screen shot the result, I added the @ltrim to get rid of any leading spaces.

@ltrim(@replace(@replace(@replace(STREET, "JL.", ""), "JLN.", ""), "JALAN", ""))

tyaskoa9090 Mon, 04/07/2014 - 00:31

Awesome!
Thanks, Brian. So, instead of using @remove, we should use @replace and use @ltrim. 
BTW, it seems that I posted at wrong category. Sorry.
Again, many thanks :)