What is the best way to suppress a single space character within a given field? 1

[montypython1]

Greetings,

What is the best way to suppress a single space character within a given field (if it is the ONLY character within that field)?

I am exporting a file into CSV format, but can't seem to figure out how to suppress the single space character within the "MidName" field if there is NO middle name. Most of the records contain a value in this field, but the few that do not are causing an error when uploading this file to another system. I've tried using the ALLTRIM function, but it still inserts a single space in the "MidName" field. My code is pretty simple (see below):

************************************
SELECT SSN, FirstName, ALLTRIM(MidName) AS MiddleName, LastName, Wages_Qtr1, PITWG_Qtr1, TaxWH_Qtr1, WagePlan ;
FROM csrEmplData_Output2 ;
INTO CURSOR csrEmplData_Output3 NOFILTER
COPY TO "C:\Test\blankmiddleinit" TYPE CSV
************************************

I certainly appreciate any suggestions.

Thanks,
Dave Higgins

 

[JRB-Bldr]

This leads me to wonder if the original space in the database was something other than a space. How can I see the ASCII representation of that particular field?

You could also write yourself a small VFP program which put the field value into a memory variable and then after determining the number of characters in the memory variable value, parse through the value character-by-character displaying the ASC() value of it.

If the memory value had NOTHING in it (it is EMPTY()), then the length of its value would be 0 and the ASC() value is also 0.

cTest = ' '
?ASC(cTest)

cTest = ''
?ASC(cTest)

Regardless, the HEX Editor that I use is: Hackman Hex Editor

Good Luck,
JRB-Bldr

 

[Mike Lewis]

To build on JRB's suggestion:

x = "Any string here"
FOR lnI = 1 TO LEN(x)
  y = SUBSTR(x, lnI, 1)
  ? y, ASC(y)
ENDFOR

Mike





__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

JRB-Bldr, it doesn't help to make this test with a string variable, variable don't act like char fields, simply because of the fixed length of char fields, but you can of course use ASC(charfield) on a C(1) field to find out the ASCII value of the byte, it will be 32, like ASC(Space(1)) is, if you store '' (an empty zero length string) into a C(1) field.

Bye, Olaf.

 

[montypython1]

Hi Olaf, Mike, Jrbbldr,

This project was an eye-opener for me. I think I am starting to understand the Character and VarChar field types better.

Although I've used VFP for a number of years, I never really understood why character fields behaved as they did. My first rude awakening (years ago) was realizing that data in certain character fields were being truncated if I created a field (ex: FieldName AS FieldName222) within a SELECT statement ... I now create my cursor before pulling in the data.

Thank you all ... I appreciate your time and suggestions ... and enjoyed learning a few new (to me) techniques.

Dave Higgins

 

[montypython1]

--- continued from earlier post ---

Forgot to mention ... I did check the data with a HEX editor and the data is indeed a regular space (HEX code 20).

Thanks,
Dave

 

[Olaf Doschke]

Well,

some simple rules for fields you create on the fly:

1. VFP takes the type/length of the first record result to define a field for it's result cursor. (which is one kind of guessing)
2. in numeric fields you can indirectly specif field with by eg muliplying with 1.0000*expression, which makes the result have 4 decimal places.
3. In strings you can enforce a result field length by PADR (or PADR)
4. VFP tries to determine needed width for character fields, if you eg concatenate two fields, also, if you make use of ALLTRIM. (which is another kind of guessing)

But you can throw all these the rules away by using CAST(), as you can simply define the outcome on your own.
You can't cast anything to anyhting else, but you have one syntax for padding, defining numeric precision and can overcome VFP's "guessing".

Preparing a Cursor is a very well defined solution, too, but if you query and append the result into your self defined cursor, you still depend on some of VFP's guessing and can be the vicitm of truncated values, as even INSERT INTO yourselfdefinedcursor FROM SELECT... will cause an intermediate result you don't control. If your SQL query part (SELECT...) has the problem of truncating strings, you can widen the result field in yourselfdefinedcursor any way you want, it won't have any curing effect.

Bye, Olaf.

 

[JRB-Bldr]

Olaf - it doesn't help to make this test with a string variable, variable don't act like char fields, simply because of the fixed length of char fields

I suggested using a memory variable because this question was about finding the ASCII value of what was suspected to be a single character (or just a few characters).

For something with more characters (such as a LARGE text string or an entire text file) I have used an Array element (instead of the memory variable) to hold the data which needs to be parsed. I have found that it is not nearly as character limited.

DIMENSION aryStr(1)
aryStr(1) = "Any string here........................."
FOR lnI = 1 TO LEN(ALLTRIM(aryStr(1)))
  y = SUBSTR(aryStr(1), lnI, 1)
  ? y, ASC(y)
ENDFOR

And both have worked well for me over the years.

Thanks,
JRB-Bldr

 

[Olaf Doschke]

JRB-Bldr,

when you were answering Daves question "How can I see the ASCII representation of that particular field?" it was already clear the field was char(1) field. So a loop is unnecessary and overcomplicated, besides copying the field value into a variable also isn't needed at all.

Also this isn't giving any insight on what happens to the midname field, if you alltrim it.

Bye, Olaf.