remove characters from a string

[jalz]

Hi Guys,

I have a field which contains the following type of data, $ 1,000,680.45 - i'd like to sanitise the field so that it removes all characters apart from numbers (0-9) and a decimal point. Are there any built in functions that will do this. I've done a quick search on the net, regex seems to be the solution, but I have no idea where to start - hoping to avoid it if I can.

Thanks as always
Jalz

[magice]

if the format will always be the same, you could so something like this

Code: Select all

replace "$"  with empty in fld "myfield"
   replace ","  with empty in fld "myfield"
   replace space with empty in fld "myfield"

If other unwanted characters are a possibility, then a repeat loop that looks at each character in the field and evaluates whether it is among "1234567890." then replaces it with empty might be the answer.

[jalz]

Hi all,

Thank you Magice for your reply. I've created a function using your suggested code, with multiple replaces which strip out the characters I've requested in my initial post. I didn't realise it would be as verbose as that, as in FileMaker you would simply nest substitute('this is my text';["Th";[""]];["my";"your"]) etc. Nevertheless the code works and is easy to read, english like which live code is all about.

The thing is, thinking in the future the currency symbol could be an euro, £ etc so thought I might as well investigate regex functions as as the more I read about them I'm certain they will be useful to me in the future, although the thought of them make me quiver. Anyway came across a site trawling this forum http://www.regexr.com which seems to have a playground so I've managed to put together the regex I need to strip out all the characters I need from a string and I've read up, it seems replaceText seems to be the command to use to apply the regex however my code is not working. Anyone advise me what I'm doing wrong please with the code I have below?

Thanks all in advance
Jalz


fld_test contains $ 123,456.00

Code: Select all

put replaceText(fld "fld_test","/([0-9.])/g","") into t
answer t

[jameshale]

Shouldn't

Code: Select all

[0-9.]

be

Code: Select all

[0-9\.] 

?

[jalz]

Hi James,

Tried that, still doesn't seem to be stripping any characters. I read on one of the earlier posts that the LiveCode engine didn't like the regex in the actual replaceText command (it could of been a early version), the poster suggested the engine prefers the regex to be in a field or variable. Im using version 7, so assuming this isn't an issue, but still wanted to take the precaution by putting it in the variable, it still doesn't seem to do anything.

Your code [0-9\.] didn't seem to produce the desired result in regexr.com

put "/([0-9\.])/g" into tRegex
put replaceText(fld "fld_test",tRegex,"") into t
answer t

[bn]

Hi Jalz,

I think it is the other way around. You have to exclude what you do not want.

Code: Select all

 put replaceText(field 1,"([a-zA-Z$ \,])","") into field 2

field 1 obviously has your "$ 123,456.00"

Please do not tell anybody that I even attempted to answer a regex question. Thank you.

Kind regards
Bernd

[Dixie]

The solution magice provided is the way I would go... there again, its a personal choice. If you are worried about that the currency symbol might change then this might work for you...

Code: Select all

on mouseUp
   lock screen
   put fld 1 into theNumbers
   replace space with empty in theNumbers
   replace comma with empty in theNumbers
   put char 2 to (the number of chars of theNumbers) of theNumbers into fld 2
end mouseUp

[bn]

OK,

if you want to go the Livecode way here is a script that is somewhat easier to maintain for longer lists since you can easily add to tToExclude.
Since it builds a tab delimited item list (because of the comma) you obviously can not take tabs out of your string. You would have to add that, also returns are not easily entered into the list.

Code: Select all

on mouseUp
   put "$ ," into tToExclude
   set the itemDelimiter to tab
   repeat with i = 1 to the number of bytes of tToExclude
      put byte i of tToExclude into item i of tItemList
   end repeat
   put field 1 into tData
   repeat for each item excludeMe in tItemList
      replace excludeMe with "" in tData
   end repeat
   put tData into field 2
end mouseUp

PS I did a benchmark on the regex and the code posted in this post
It does compare quite favorable.
For 40.000 lines of $ 123,456.00 it took
about 180 milliseconds for regex and 120 milliseconds for Livecode. This was using 6.7.2 rc 2
whereas LC 7.0.2 took about 420 milliseconds for the liveCode code and did not like regex at all, it hung and I had to force quit with above regex example.
Kind regards
Bernd

[Dixie]

Nice one Bernd... Good to see that you are putting to use the changes in the dictionary !... I looked at your script and had to run to the dictionary to read it for myself...

[bn]

@ Dixie, thanks

@ Jalz

I asked a widely known regex expert from France and of course I was wrong because it would not take account of other characters. It worked but only in this specific case
The easiest way is to exclude everything except what you want, actually close to what you posted. Only the carret ^ was missing which in case of replaceText means everything but. Since this is not match but replace.
Learned a lot. By the way http://www.regexr.com/ is very helpful for testing. Thanks for the link.

the line should be

Code: Select all

 put replaceText(field 1,"[^\d.\n]", empty) into field 2

where \n is lineFeed which I used in processing the long list of numbers. Could be omitted if it is only one line.


Kind regards
Bernd

[jalz]

Thank you all for the replies, interesting subject.

@bn, thanks finally got regex working in my code. Was interested in your benchmarks, seems like Livecode interprets its own commands more efficiently even though they are verbose compared to the regex.

Now that I have both methods working working (magice, regex), not sure which one i will end up with, but its nice to have the choice - and I can say I kinda understand a little more about regex although that carat character caught me out - something to remember.

I think I'm going to check out the code you posted earlier, quite like the idea of maintaining an exclude list, I think that would work quite nicely for my app.

Thanks again

Jalz