Extract from string 1

[abenitez77]

I want to get the location of a number in a string that is in a column in my table. I need to extract the FUB 058508 from the first line, FUBC059171 from second line, FUBC059180 from the 3rd line, FUBC059381 from the 4th line. I have a new field that I want to copy those values to. What command can i use to do this? I also want to copy the string from the beggining to where FUB starts and put that value in a new column. How do i do this?

mystring:

SCHUYLKILL LIVING MAGAZ FUB 058508 CD 6 4070 6 1,100.00

ATI FUBC059171 CD 6 4034 2 -400.00

AMERICAN KIDNEY STONE M FUBC059180 CD 6 4034 11 -15,000.00

PHILIPS MEDICAL SYSTEMS FUBC059381 CD 6 4045 104 1,784.12

 

[mm0000]

The example below assumes that the string starts with 'FUB' and is always 10 characters long.

xstr='SCHUYLKILL LIVING MAGAZ FUB 058508 CD 6 4070 6 1,100.00'
xsubstr = SUBSTR(xstr,AT('FUB',xstr,1),10)
replace fieldname with xsubstr

 

[mmerlinn]

[ ]

mm0000 answered the first part of your question. Expanding on his solution here is how you can get both parts you need.

[tt][blue]
xstr='SCHUYLKILL LIVING MAGAZ FUB 058508 CD 6 4070 6 1,100.00'
xlocation = AT("FUB", xstr, 1)

xnumber = SUBSTR(xstr, xlocation, 10)
xcompany = LEFT(xstr, xlocation - 1)

[/blue][/tt]

The third argument is not needed in AT() if FUB always occurs immediately after the company name. If FUB ever occurs in a company name, then you will not get correct results using this.

xnumber assumes that the number is always 10 characters long as noted in the previous post.

Once you have the two values, you can stuff them into any field you want.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[Olaf Doschke]

First: Is this from a file and are the values perhaps actually tab delimited? Then you should be able to import the file by IMPORT or APPEND into a cursor. That would be much simpler than any string processing attempts.

Second: Strextract() can easily extract the parts of the lines you want. Like mm0000 I assume FUB to be 10 chars long and the part before it seems to have a max length of 24. This makes the scheme of the curTest cursor I create in the following code and can of course be extended:

Local lcText, lnCount, lcPrefub, lcFub, laLines[1]

* The following could be replaced by lcText = FiletoStr("inputfile.txt")
Text TO lcText Noshow
SCHUYLKILL LIVING MAGAZ FUB 058508 CD 6 4070 6 1,100.00
ATI FUBC059171 CD 6 4034 2 -400.00    
AMERICAN KIDNEY STONE M FUBC059180 CD 6 4034 11 -15,000.00    
PHILIPS MEDICAL SYSTEMS FUBC059381 CD 6 4045 104 1,784.12
EndText

Create Cursor curTest (cLine C(240), cPreFub C(24), cFub C(10))

For lnCount = 1 To ALines(laLines,lcText)
   lcLine = laLines[lnCount]
   lcPrefub = StrExtract(lcLine,"","FUB",1,1)
   lcFub = StrExtract(lcLine,"FUB","",1,1+2+4)
   lcFub = Left(lcFub,10)
   Insert Into curTest (cLine, cPrefub, cFub) values (lcLine, lcPrefub, lcFub)
   ? "Pre to FUB is:", lcPrefub, ", FUB is:", lcFub
EndFor lnCount

Browse Nowait

You will need to resize the first column "cLine" of the browse window to see the extracted parts.

Bye, Olaf.

 

[GriffMG]

I would watch out for relying on searching for soley 'FUB', it could well be in the name element that precedes the number.

I might be tempted to work backwards from ' CD 6 ' and test for the 'FUB' being the first three of the 10 character block before that...

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[Olaf Doschke]

Griff, in the 6 example lines FUB is always there, I think it's integral part of the FUB number. CD 6 might as well be CD 1,2,3,4,5 etc in other lines.

Anyway without any specs and just samples you can't do definitive assertions anyway. I add to my assumptions, that FUB is present in all lines and a full line always is less than 240 chars and my code is valid, if the assumptions are correct, but only then.

Besides that I think the original file is tab delimited and then everything is much easier anyway and doable with IMPORT File or APPEND FROM File or you could open this in Excel.

Bye, Olaf.

 

[Olaf Doschke]

Also there already is a bit of security in my code, as I take the full line into a field cLine: If you browse the cursor for empty(cFub) you'll find lines, in which a FUB is missing and could decide what to do with these lines.

Bye, Olaf.

 

[Olaf Doschke]

Actually there is another pitfall in that "FUB" might also be in the left part of a line before the actual FUB code. Then you'd need to find the last FUB of each line, which is possible with OCCURS() and the nOccurrence parameter of STREXTRACT.

I stringly suggest you first test if the file actually is tab delimited before using any of the code including mine.

Create Cursor curImport (cField1 C(240), cField2 C(240), cField3 C(240), cField4 C(240), cField5 C(240)) && you might add more fields

Append From (Getfile()) TYPE DELIMITED WITH TAB

If you choose your input file and the APPEND only fills the first column cField1 the file is not tab delimited.

If values spread to cField2 and higher you have the separated values and can go from there, eg convert the char fields to other field types.

Bye, Olaf.

 

[abenitez77]

It is not tab delimited. I updated the column with the FUB numbers. But now i have to put another value into the same column i put the FUB values.

METROPOLITAN LIFE 080708 PJ 6 4078 56 2,309.74
MERRILL LYNCH 83886W12081708 PJ 6 4091 72 950.00
ENESCO GROUP INC 12406180 PJ 6 4377 6 64.02
RINGTOWN FARM EQUIPMENT 15353/16011 PJ 7 1038 10 189.15
PINE CREEK LANDSCAPING 537 PJ 7 1045 173 485.00
J B ELECTRIC CORP 0010921IN/16069 PJ 6 4257 301 132.39

I need to get the number before the PJ and as you can see it varies in length and sometimes it has a slash in the middle.
This is the desired values from above to replace into my other column

080708
83886W12081708
12406180
15353/16011
537
0010921IN/16069

I was trying a combination of substr with AT but I can only get the ending part which starts with PJ. I was thinking of getting the space before the starting point, but not sure how to get to that point. Please help.

 

[abenitez77]

Thanks, now I have another value I must get from that string. The file I imported was not tab or comma delimited.
I updated the column with the FUB numbers. But now i have to put another value into the same column i put the FUB values.

METROPOLITAN LIFE 080708 PJ 6 4078 56 2,309.74
MERRILL LYNCH 83886W12081708 PJ 6 4091 72 950.00
ENESCO GROUP INC 12406180 PJ 6 4377 6 64.02
RINGTOWN FARM EQUIPMENT 15353/16011 PJ 7 1038 10 189.15
PINE CREEK LANDSCAPING 537 PJ 7 1045 173 485.00
J B ELECTRIC CORP 0010921IN/16069 PJ 6 4257 301 132.39

I need to get the number before the PJ and as you can see it varies in length and sometimes it has a slash in the middle.
This is the desired values from above to replace into my other column

080708
83886W12081708
12406180
15353/16011
537
0010921IN/16069

I was trying a combination of substr with AT but I can only get the ending part which starts with PJ. I was thinking of getting the space before the starting point, but not sure how to get to that point. Please help.

 

[mm0000]

This assumes that there is no space in the 'desired values'

xstr = 'METROPOLITAN LIFE 080708 PJ 6 4078 56 2,309.74 '
xpos = AT(' PJ',xstr)-1
xoccurs = OCCURS(' ',SUBSTR(xstr,1,xpos))
xsppos = ATC(' ',SUBSTR(xstr,1,xpos),xoccurs)
xsubstr=SUBSTR(xstr,xsppos,xpos-xsppos+1)

 

[GriffMG]

I would have worked from the end to the beginning:

clear
m.string = "J B ELECTRIC CORP 0010921IN/16069 PJ 6 4257 301 132.39   "

m.BitImInterestedin = ""
** look for last ' PJ '
m.PJPosn = Rat(" PJ ",m.string)
if m.PJPosn > 0
   ** if there is one, look for the space before that
   m.PrevSpace = Rat(" ",Left(m.string,m.PJPosn-1))
   if m.PrevSpace > 0
      ** if there is one, take the bit in the middle
      m.BitImInterestedin = AllTrim(Substr(m.string,m.PrevSpace,m.PJPosn-m.PrevSpace))
   endif
ENDIF
? "["+m.BitImInterestedin+"]"

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

 

[mmerlinn]

[&npsp;]

I would do this slightly differently than mm0000:

[tt][blue]
xstr = 'METROPOLITAN LIFE 080708 PJ 6 4078 56 2,309.74 '
xpos = AT(' PJ', xstr) - 1
xleft = LEFT(xstr, xpos)
xpos2 = RAT(' ', xleft) + 1
xresult = SUBSTR(xleft, xpos2)
[/blue][/tt]

This assumes that ' PJ' only occurs ONCE in the string and that there are no spaces within the number sequence you want to extract.

mmerlinn

http://mmerlinn.com

Poor people do not hire employees. If you soak the rich, who are you going to work for?

"We've found by experience that people who are careless and sloppy writers are usually also careless and sloppy at thinking and coding. Answering questions for careless and sloppy thinkers is not rewarding." - Eric Raymond

 

[abenitez77]

I ended up using this:

Replace refjour with GETWORDNUM(STREXTR(Descmemo,' ','PJ'), GETWORDCOUNT(STREXTR(Descmemo,' ','PJ'))) FOR AT('PJ', ALLTRIM(descmemo)) <> 0

Thanks everyone

 

[Olaf Doschke]

I was about to suggest your approach, abenitez77.

using STREXTR() to extract anything before " PJ" as an end delimiter and taking the last "word" of that intermediate result.

Bye, Olaf.

 

[cbasoz]

http://www.foxite.com/archives/0000282024.htm

Cetin Basoz
MS Foxpro MVP, MCP

 

[GriffMG]

Is there not a risk that PJ could fall BEFORE the number being sought?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.