Finding 2 sets of numbers and transferring each to separate field 2

[Phil Thoms]

Hello,
I wish to find the value of 2 numbers in a text field of variable length. These numbers appear in brackets at the end of the text:-
xxxxxxxxxxxxxx (2,9.5mm)
xxxxxxxxxxxxxxxxxxxxxxxx(10,10mm)

The first figure can be 2 chars in length and the last figure can be whole or with decimal of .5
Once found I wish to transfer each to a field of their own (mm does not need to be transferred).

Thanks for your help.

 

[Tore Bleken]

lcOldValue = 'xxxxxxxxxxxxxx (2,9.5mm)'
lnNewValue1 = val(getwordnum(strextract(lcOldValue,'(',')'),1,','))
lnNewValue2 = val(getwordnum(strextract(lcOldValue,'(',')'),2,','))

 

[Olaf Doschke]

Decomposing strings is easy with STREXTRACT.

Step 1: extract paranthesis/bracket at the end.
[pre]lcValues = StrExtract(text,"(",")",OCCURS("(",text),0)[/pre]

Step 2: Separate values
[pre]lnVal1 = Val(getwordnum(lcValues,1,",")
lnVal2 = Val(getwordnum(lcValues,2,",")[/pre]

Edit: Changed Eval to Val. Tore is right about VAL being better suited, EVAL would fail on the mm, VAL simply stops parsing out the numeric value at it.

I'd double check, if there are no rows without such data, eg via
[pre]BROWSE FOR NOT "(,)" $ CHRTRAN(text,CHRTRAN(text,"(,)",""),"")[/pre]

In all code replace "text" with the name of the field containing "xxxxxxxxxxxxxxxxx(num1,num2)"

Bye, Olaf.

 

[Phil Thoms]

Hello,
Sorry I should have mentioned that I am working with VFP version 6 which doesn't have the getwordnum function.

 

[Olaf Doschke]

Well, you have foxtools.fll
SET LIBRARY TO foxtools.fll
Now you have GETWORDNUM.

Bye, Olaf.

 

[Phil Thoms]

Hello Olaf,
I assume STREXTRACT is also in foxtools.dll ?

Thanks

 

[Tore Bleken]

You chose not to upgrade, now you pay the price. Unfortunately!

 

[Mike Lewis]

SET LIBRARY TO foxtools.fll
Now you have GETWORDNUM.

Not quite. In Foxtools, WORDNUM() is the equivalent of GETWORDNUM(), and WORDS() is the equivalent of GETWORDCOUNT().

I assume STREXTRACT is also in foxtools.dll ?

No. And I don't know any equivalent.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

This gets a bit more legacy, then. The outcome is even a bit shorter, though sloppier.

You need to get the position of the rightmost opening bracket, RAT("(",text) and get the sub string starting at that position+1, eg SUBSTR(text,RAT("(",text)+1).
To get the same result as STREXTRACT you'd need to ALLTRIM that and cut off the closing bracket, but VAL() will ignore all trailing characters anyway, as it also ignores mm.

Step 0: use the foxtools library
[pre]SET LIBRARY TO foxtools.fll[/pre]

Step 1: extract paranthesis/bracket at the end, trailing chars not removed.
[pre]lcValues = SUBSTR(text,RAT("(",text)+1)[/pre]

Step 2: Separate values
[pre]lnVal1 = Val(Wordnum(lcValues,1,",")
lnVal2 = Val(Wordnum(lcValues,2,",")[/pre]
Bye, Olaf.

 

[vgulielmus]

This alternative solution use only AT(), VAL() and SUBSTR()

LOCAL lcOldValue, lnNewValue1, lnNewValue2

lcOldValue = 'xxxxxxxxxxxxxx (2,9.5mm)'
getvalues(m.lcOldValue, @lnNewValue1, @lnNewValue2)
?m.lnNewValue1, m.lnNewValue2

lcOldValue = 'xxxxxxxxxxxxxxxxxxxxxxxx(10,10mm)'
getvalues(m.lcOldValue, @lnNewValue1, @lnNewValue2)
?m.lnNewValue1, m.lnNewValue2

PROCEDURE getvalues
	LPARAMETERS  lcOldValue, lnNewValue1, lnNewValue2
	LOCAL ln1, ln2, ln3
	ln1 = AT("(", m.lcOldValue) + 1
	ln2 = AT(",", m.lcOldValue) 
	lnNewValue1 = VAL(SUBSTR(m.lcOldValue, m.ln1, m.ln2 - m.ln1))
	ln3 = AT(")", m.lcOldValue) - 1
	lnNewValue2 = VAL(SUBSTR(m.lcOldValue, m.ln2 + 1, m.ln3 - m.ln2))
RETURN

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

I wouldn't rely on the xxxxxxxxxxxxxxxxxxxx part not having any brackets or comma in it. I recommend cutting off at RAT("(",text), not at AT("(",text).

Bye, Olaf.

 

[vgulielmus]

I also believe it's safer to use RAT() instead of AT()

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Phil Thoms]

Many thanks for all your help. Will let you know the outcome.

 

[Phil Thoms]

Olaf,
I have typed SET LIBRARY TO foxtools.fll
but your method is not finding the StrExtract.prg file

Any suggestions?

Thanks

 

[Tore Bleken]

Foxtools.fll contains some of the functions which later have been built into VFP, unfortunately StrExtract isn't one of them. This means that you must use one of the other solutions.

For a list of functions contained in foxtools.fll, see

http://www.foxtools.com/foxtoolsfll-help-file

 

[Phil Thoms]

Hello vguliemus,
Your code only works with ? but not from the database.

Thanks

 

[vgulielmus]

I suppose you haven't removed the mdot before lcOldValue.
In my small demo, lcOldValue is a memory variable

LOCAL lcOldValue, lnNewValue1, lnNewValue2
lcOldValue = 'xxxxxxxxxxxxxx (2,9.5mm)'

If I'm not mistaken, you have to remove the mdot before lcOldValue here :
Insted of :
getvalues(m.lcOldValue, @lnNewValue1, @lnNewValue2)

please try :
getvalues(lcOldValue, @lnNewValue1, @lnNewValue2)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

Phil, I already changed my suggestion to work without strextract. You should catch up reading, perhaps.

Bye, Olaf.

 

[Phil Thoms]

Hi Olaf,
Sorry, rushing around too much.
Your step 2 comes up with an error 'Function name is required'.

Thanks

 

[Phil Thoms]

Hi vguliemus,
Have tried what you said but does not work. Would it be necessary to remove 'm.' in procedure code also?
Also what the @ signs for, have not seen this used on variables directly.

Thanks