Quickest way to trim all fields from a query? 1

[theThirdElement]

Hello,

I'm extracting some > 30 fields from a cursor, and I would like to get all of the fields ALLTRIM()'ed. Is there a quick way to do it rather than wrapping each field with ALLTRIM()?

Thanks,

Ed

 

[baltman]

for i = 1 to afields(laTemp)
 IF laTemp(i,2)=[C]
  replace all (laTemp(i,1)) with alltr(eval(laTemp(i,1)))
 endif 
endfor

Brian

 

[Mike Lewis]

Ed,

Is there a quick way to do it rather than wrapping each field with ALLTRIM()?

The code Brian gave you is not only a neat way of achieving your goal, but it is more or less the only way.

If you wrap each field with an ALLTRIM(), the SELECT statement wouldn't know how wide to make the fields in the result set. In each case, it would simply take the width of the field from the first record in the input cursor. By using ALLTRIM() in a SELECT, some of the resulting fields would be truncated and others would be padded with spaces.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[DSummZZZ]

I'm confused. If you have an item 10 characters long in a field with a width of 40, and you replace it with an alltrim'ed item, the field width is still 40.

If you're just wanting to throw them into a single variable or something, you could use brian's code like this:

MyString  = ''
for i = 1 to afields(laTemp)
 IF laTemp(i,2)=[C]
  MyString = MyString + ALLTRIM(Evaluate(laTemp(i,1)))
 endif
endfor
?MyString

-or-

MyString = ''
for i = 1 to afields(laTemp)
  MyString = MyString - transform(Evaluate(laTemp(i,1)))
endfor
?MyString

Another thing I have done in the past is export the cursor to a text file delimited, and read it back in. Not as pretty but it works. VFP trims the result:

MyString = ''
SELECT TheCursor
COPY TO SomeFile.TXT DELIMITED 
nHandle = Fopen('SomeFile.TXT')
DO WHILE !FEOF(nHandle)
   MyString = Chrtran(Fgets(nHandle), '",', '')
   ?MyString 
ENDDO
FCLOSE(nHandle)

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[Mike Lewis]

Dave,

If you have an item 10 characters long in a field with a width of 40, and you replace it with an alltrim'ed item, the field width is still 40.

Of course that's right. But surely Ed was doing SELECT to extract the trimmed fields into a cursor?

Now I have looked back on his question, he didn't explictly say that. In fact, I am not sure now exactly what is he trying to achieve.

Ed, perhaps you could clarify?

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[craigsboyd]

A Scatter Memvar parameter/clause that would allow you to specify a function to run on all the fields would be kind of nice actually. I can think of a few uses for this...the first possiblities that come to mind are Alltrim() as spoken of here and Transform() when I want everything converted to a string.

Mike you made a good point here about the field lengths being determined by the first record's field when a variable length expression like Alltrim() is used in a SQL Select statement. It's a little trap I fell into many a time early in my VFP programming career, and to be honest I can still get tripped up on stuff like if I'm not careful.

Dave your point is also quite good, I mean what is the sense in removing leading and trailing spaces from a field only to throw it back into the same character field where the excess field length will become trailing spaces again anyways.

Ed, there are some shortcuts for trimming field values if you are putting them in a report or using them as the controlsource or value for a textbox. But as far as initializing your variables with the field values minus spaces, the only automated way is to use something similar to what has already been suggested...some type of looping structure and the indice of the field...you would have to use an array though for this to be effective and worthwhile, otherwise you will need to use macro substitution and name your variables something like MyVar1, MyVar2, MyVar3.

In the end it may just be easier to write those 30 alltrim statments, as painful as it may seem. A little cut-n-paste and you'll be done in no time.

http://www.sweetpotatosoftware.com

 

[theThirdElement]

Hello,

Thank you all for your replies. My original intention was that after the cursor is made from a select statement, when I get the field values from that cursor, I want the values to be trimmed. There is, obviously, no point in alltriming the fields in the select statement beforehand, and then chucking them in the cursor (as the field width is not changed).

And yes, Craig, I'm using the values to make a report or something similar.

I think Brian's is a very good solution to my problem, and I thought about something like that, although I was expecting some sort of VFP hack or trick that will trim all values within a simple function call.

Thank you again for your replies!

Ed