Re: keeping blanks out of the data

>>( Rob Yslas) writes: >>>I am using the standard engine and would like to know if there is a >>>way for me to keep the blanks at the end of a form from being saved (attribute). >>>The user likes to space bar for deletion of mistakes rather then using ^d. >>>I would also like to know if there is an easy way to remove existing blanks from >>>the end of fields in the database or set fields with only blanks to a null. >dsimon@jupiter.noname writes: >>Unless you are using On-Line and var-chars, it shouldn't really matter, since >>"Hi" and "Hi " are stored the same way, the only time it would make a difference >>is that there is a difference between NULL and " ". >> >>As to clearing blank fields to NULL, I would suggest an UPDATE similar to this: >> >>UPDATE x SET cola = NULL >> WHERE cola MATCHES " ">> >>You should probably play with that to see what the exact syntax you need is. Be aware >>that storing as NULL is not going to change the size of you database files...CISAM >>files are fixed length records, a char(20) is going to take up 20 bytes, regardless >>of what is stored in those bytes. (Nancy Suffron) writes: >What Don says is true, but please note that when using PERFORM, you >cannot insert NULL into a field. Passing a field without entering >data will result in a field full of blanks. Sorry to be so slow in following up on this. By now, Rob has probably figured out how to change the Blanks to NULLs or vice versa or whatever. I just thought I'd contribute HOW to get blank strings or nulls in where you want them via Perform. Contrary to what Nancy wrote, unless your CHAR field is defined NOT NULL, in most cases, char fields left blank will become NULLs when inserted via Perform. There are some exceptions. I'd better warn you that reading this may make you wish you hadn't asked. TechInfo # 3547 These are the results of trying to insert spaces or nulls into a database with any combination of the following restrictions: 1) Field is defined in table as NOT NULL 2) Form is specified WITHOUT NULL INPUT 3) Field attribute in form REQUIRED 4) Field attribute in form DEFAULT = "" I tried inserting nothing (<CR> w/ no input) or spaces (3, for a CHAR(3) field). Sometimes a NULL was inserted, sometimes a blank string was inserted, and sometimes an error message was returned. Note that a string of blanks is treated the same as a string of zero length, so comparing the string to "" or to " " will test true. The error messages returned were: (A) This field requires an entered value. (B) The column "x" does not allow null values. |----------|-------------------------------|-----------------------------| | Table | Form | Result from Inserting: | | NOT NULL | W/O NULL | REQUIRED | DEFAULT | <CR> | 3 spaces | |----------|----------|----------|---------|--------------|--------------| 1. | N | N | N | N | NULL | NULL | |----------|----------|----------|---------|--------------|--------------| 2. | N | N | N | Y | "" | NULL | |----------|----------|----------|---------|--------------|--------------| 3. | N | N | Y | N | error A | NULL | |----------|----------|----------|---------|--------------|--------------| 4. | N | N | Y | Y | "" | NULL | |----------|----------|----------|---------|--------------|--------------| 5. | N | Y | N | N | NULL | NULL | |----------|----------|----------|---------|--------------|--------------| 6. | N | Y | N | Y | "" | NULL | |----------|----------|----------|---------|--------------|--------------| 7. | N | Y | Y | N | error A | NULL | |----------|----------|----------|---------|--------------|--------------| 8. | N | Y | Y | Y | "" | NULL | |----------|----------|----------|---------|--------------|--------------| 9. | Y | N | N | N | error B | error B | |----------|----------|----------|---------|--------------|--------------| 10.| Y | N | N | Y | "" | error B | |----------|----------|----------|---------|--------------|--------------| 11.| Y | N | Y | N | error A | error B | |----------|----------|----------|---------|--------------|--------------| 12.| Y | N | Y | Y | "" | error B | |----------|----------|----------|---------|--------------|--------------| 13.| Y | Y | N | N | "" | "" | |----------|----------|----------|---------|--------------|--------------| 14.| Y | Y | N | Y | "" | "" | |----------|----------|----------|---------|--------------|--------------| 15.| Y | Y | Y | N | error A | "" | |----------|----------|----------|---------|--------------|--------------| 16.| Y | Y | Y | Y | error A | "" | |----------|----------|----------|---------|--------------|--------------| A quick lesson on reading this chart: The 1st position indicates whether the column is not defined NOT NULL (ie, Y means the column is NOT NULL, N means the column accepts NULL). The 2nd position indicates whether the form has the WITHOUT NULL INPUT option (eg. DATABASE stores WITHOUT NULL INPUT). The 3rd position indicates whether the form has field attribute REQUIRED. The 4th position indicates whether the form has field attribute DEFAULT = "". The 5th position indicates what you get in the table if you hit <RETURN> in this field. The 6th position indicates what you get in the table if you type 3 spaces in this field. For example, on line 4, which reads N N Y Y "" NULL: The column is not defined NOT NULL (ie, it accepts nulls), The form does not have DATABASE x WITHOUT NULL INPUT, The field attributes include REQUIRED, The field attributes include DEFAULT = "", If you hit <RETURN> in this field, a blank string will be inserted. If you type 3 spaces in this field, the column will be NULL in the database. If you look at the 8th entry down, which differs in that the form has DATABASE x WITHOUT NULL INPUT, the results are the same. In fact, if the column accepts NULLS, there is no apparent difference from having the WITHOUT NULL INPUT clause in the form. So what is the point of all this? As many people have discovered, if a field is NULL, any comparison (except IS NULL) will evaluate to FALSE, even if the other value is also NULL. So if A is NULL and B is NULL, A = B will still test FALSE. If the intention of the user is for that to test TRUE, the workaround of (A = B) OR (A IS N