Parsing Problem 2

[keepingbusy]

Hi all

Can anyone suggest a sollution for the following?

In a table there are list of artists in a field called ARTISTTEMP and in a format such as DIAMOND,NEIL or ROSS,DIANA

I use the following to reverse the names and remove the comma with the result NEIL DIAMOND or DIANA ROSS

SCAN
  STORE SPACE(100)  TO mfull
  STORE ARTISTTEMP  TO mfull
  STORE SPACE(50)   TO nSplit, msur, mfor
  nSplit = AT(",",mfull)
  msur = LEFT(mfull, nSplit - 1)
  mfor = SUBSTR(mfull, nSplit + 1)
  REPLACE ARTISTTEMP WITH ALLTRIM(mfor)+" "+ALLTRIM(msur)
ENDSCAN

When a record contains more than one artist such as BENSON,GEORGE / JARREAU,AL I am getting results that look like GEORGE / JARREAU AL BENSON when it shoud read GEORGE BENSON / AL JARREAU

Is there a way to overcome this?

I am using Version 9

Thank you in anticipation.

Lee


Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[Imaginecorp]

Lee:

Thinking about this now that I have some time, try this and see if it works: Rather than a Scan

*** t = "Joel,Billy / Santana,Carlos"
*** m = STREXTRACT(t,",","/")+""+STREXTRACT(t,"",",",1)+" / "+;
*** STREXTRACT(t,",","",2)+""+STREXTRACT(t,"/",",")
*? m

Replace ARTISTTEMP With ;
	ICASE((At("/",Alltrim(ARTISTTEMP)) > 0 And At(",",Alltrim(ARTISTTEMP)) > 0);
	And (Not Inlist(Alltrim(ARTISTTEMP),":","(",")")),;
	(Strextract(Alltrim(ARTISTTEMP),",","/")+""+Strextract(Alltrim(ARTISTTEMP),"",",",1)+" / "+;
	STREXTRACT(Alltrim(ARTISTTEMP),",","",2)+""+Strextract(Alltrim(ARTISTTEMP),"/",",")),;
	AT(",",Alltrim(ARTISTTEMP)) > 0,Strextract(Alltrim(ARTISTTEMP),",") +" "+ Strextract(Alltrim(ARTISTTEMP),"",","),;
	Alltrim(ARTISTTEMP)) All In <<tablename>>

Again: this is to give you an idea, you will have to fine tune

 

[keepingbusy]

Hi Imagninecorp

try this and see if it works: Rather than a Scan

Sorry, are you saying this will work outside of a SCAN..ENDSCAN or some other way?

I notice the

All In <<tablename>>

The table name is PRODUCTS

 All in PRODUCTS

Is that what you mean?

(I am away now until Monday, so I'll post back then)

Thank you

Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[Imaginecorp]

Hello Lee:

You do Not need a Scan..EndScan

Select 0
Use Products
Replace Artisttemp With ;
	ICASE((At("/",Alltrim(Artisttemp)) > 0 And At(",",Alltrim(Artisttemp)) > 0);
	And (Not Inlist(Alltrim(Artisttemp),":","(",")")),;
	(Strextract(Alltrim(Artisttemp),",","/")+""+Strextract(Alltrim(Artisttemp),"",",",1)+" / "+;
	STREXTRACT(Alltrim(Artisttemp),",","",2)+""+Strextract(Alltrim(Artisttemp),"/",",")),;
	AT(",",Alltrim(Artisttemp)) > 0,Strextract(Alltrim(Artisttemp),",") +" "+;
	Strextract(Alltrim(Artisttemp),"",","),;
	Alltrim(Artisttemp)) All In products

When you specify "In " the table could be open in another work space and not selected. IN makes sure the Replace is done in the right table i.e. Products here

Again: this is to give you an idea, You will have to fine tune

 

[Imaginecorp]

Should have mentioned:
ICASE will not work in VFP6, Only in VFP8 & 9

ICASE is similar to an IIF.

 

[mmerlinn]

[ ]
I have just corrected the problems with the ATXLEFT() & ATXRIGHT() UDFs in FAQ184-5975 so they should work properly as described above.

Sorry for not finding the problems sooner.


mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[keepingbusy]

Hi Imaginecorp

Haven't had a chance to try out your suggestion yet but will do sometime later. The software is working now and is acceptable so as we progress, I'll introduce the latter code at a later date.

Thanks again
Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk

 

[ilyad]

<i>Should have mentioned:
ICASE will not work in VFP6, Only in VFP8 & 9

ICASE is similar to an IIF.</i>

In VFP9 only.

 

[keepingbusy]

ilyad

Thank you
Lee

Windows XP
Visual FoxPro Version 6 & 9

http://www.customsoftware.co.uk