string trans

[pwright]

I have a data file of 100k+ records. After concatonating the address into 1 field, some addresses have multiple spaces (3-5) resulting from individual fields in the " parsed" address being blank.

here is my code for the concatonate:
set address = allt(stno) + " " + allt(stpre) + " " + allt(stname) + " " + allt(sttype) + " " + allt(stpost) + " " + allt(apttype) + " " + allt(aptno)

for those addresses that don't have all these elements, it creates multiple spaces. I'm looking for one line of code that can reduce those spaces down to a single space regardless of how many blanks are there, 2, 3, or 5. right now we use a line of code for each "blank" scenario.

Thanks!

 

[weedz]

Use an immediate IF. Like:

IIF(!EMPTY(String), ALLTRIM(String) + space(1), SPACE(0))

So this means, if string is not empty, add the string + one space, otherwise add zero spaces.

So in your example:

set address = IIF(!EMPTY(stno), allt(stno) + SPACE(1), SPACE(0) + IIF(!EMPTY(stpre), allt(stpre) + SPACE(1), SPACE(0)) etc.

HTH,
Weedz (Edward W.F. Veld)
My private project:

http://www.crowncap.demon.nl\info\crwnbase

Download the CrownBase source code !!

 

[dgrewe]

or try the strtran command
set address = strtran( allt(stno) + " " + allt(stpre) + " " + allt(stname) + " " + allt(sttype) + " " + allt(stpost) + " " + allt(apttype) + " " + allt(aptno)) , " " , " &quot

&& First one is 2 spaces, the second one is one space)
David W. Grewe
Dave@internationalbid.com

 

[lucindom]

* Join all Strings and replace 2 spaces with 1 space.
set address = STRTRAN(stno+" "+stpre+" "+stname+" "+sttype+" "+stpost+" "apttype+" "+aptno," "," &quot