Hi,

I have Pervasive 10 with the latest patch installed (10.01.070) 

I had a table with a CHAR(20) column that contained some data. I altered the column to a VARCHAR(20), no errors where reported.

Now, when I try to run a query to retrieve a row:

SELECT * FROM TABLENAME WHERE COLUMN1 LIKE 'VALUE'

the query returns 0 rows.

The only way I got to get the value was by doing:

SELECT * FROM TABLENAME WHERE RTrim(LTrim(COLUMN1)) LIKE 'VALUE'

 If I enter a new row, I can run the first query without any problem.

How is this possible? How can I fix the table to make the first query to run with all rows??

Thanks. 

Hi,

 I've just used the DBF Builder tool, and discovered that for the newly inserted row, the value on the file is the string I entered followed by a number of "zero" characters up to the size of the varchar. This "zero" character is a special character showed in the DBF Builder as a zero crossed in diagonal.

The rest of the rows that contained data before I run the Alter table command to change them to VARCHAR only contain that special "zero" character at the very end of the field.

Example:

HOUSE0000000000

HOUSE              0
 

Regards 

 You are correct -- the CHAR data type is space-padded, and the VARCHAR data type is null-terminated.  When you alter the table structure, the data is NOT converted automatically.  (Should it be?  This could be a huge discussion in itself.)

To convert your data, issue this statement:

    UPDATE TableName SET Field1 = LTRIM(RTRIM(Field1))

Well,

About the automatic conversion of data when the data type is changed, I think that this is something very basic. If someone changes the datatype of a column, it is because he/she wants to store the data under the new format. It doesn't make any sense have a column with a datatype and the data on the column to have a different format.

The problem is that some columns are PK or linked to FK, and the UPDATE with Ltrim(rtrim()) fails. Any suggestion?

One more thing: why is not enough with a Rtrim and a Ltrim is needed??

Regards 

Hopefully, someone from Pervasive will pick up this thread to answer these important questions... 

Try "SELECT * FROM TABLENAME WHERE COLUMN1 LIKE 'VALUE%'  "  for the query you indicated would not bring back values.  

The behavior of how the data is formatted when you go between char and varchar is the same behavior as MS's SQLServer.