Using PADL to Update records

[breezett93]

Hello all,

I need to make two updates to one column. First, I need to ALLTRIM one column in every record. Second, in the same column, I need to add a 0 to the left in every record where the value is < 100. That way, all values for that column will have 3 digits and have no blanks.

So I thought using UPDATE would be perfect. This is how I understand the command:

UPDATE tableName
SET columnName = x
WHERE condition

I'm wondering if I can use the ALLTRIM and PADL functions as the expressions.
UPDATE tableName
SET columnName = columnName + PADL(columnName, 3, "0")
WHERE columnName < 100

Thank you.

 

[Mike Lewis]

You are on the right track, but not quite there.

First, keep in mind that the field is a fixed width*. So trimming blanks from the [tt]right-hand end[/tt] will have effect. VFP will simply add the blanks back to make the field the correct width. If you want to trim from the left-hand end - in every record - you would do this:
[tt]
UPDATE MyTable SET MyColumn = ALLTRIM(MyColumn)[/tt]

For the second requirement - padding to the left with zeroes - you would do this:

[tt]UPDATE MyTable SET MyColumn = PADL(MyColumn, 3, "0");
WHERE Value < 100[/tt]

But that would still give you blanks at the right-hand end, to fill the width of the field.

(* Assuming it's not a memo field.)

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

You can always :
1. Copy the DBF
2. test your update
and
3. finally revert to the copy or be fine with the result

Since you want to trim and pad the value, you'd not want to add the unmodified to the padded value, that way you'd have the value twice - left and right.
So for a simpler test try the expression with some value in the command window:

value = "1"
? value + PADL(value,3,"0")

This will not result in 001 but 1001, can you see why? If not try adding ? "1"+"001".
Make further changes until you arrive at PADL(ALLTRIM(columnname),3,"0").

Now you can build your final UPDATE statement. You understood its mechanics, and since you want to update all records, your WHERE condition could either be WHERE .T. or you simply don't specify WHERE at all, then you also update all records. That differs from REPLACE, where the default scope is the NEXT 1 (meaning current 1) record only, the default scope of UPDATE is the whole table. So you're right to be cautious about this and not start with the table itself, as you might get irreversible updates and there is no undo.

Bye, Olaf.

 

[Olaf Doschke]

Besides: If the column type is not char or memo, but a numeric type, you can't pad it to the right, numbers are always stored in binary format without leading zeros.
That can also be seen when testing:
value =1
? value + PADL(value,3,"0")

This'll give an error, you can't add a string to a number.
? PADL(value,3,"0")
This alone will work, as PADL accepts both char and numeric values in its first parameter. But you can't store a string into a numeric field.

Bye, Olaf.

 

[Mike Lewis]

Yes, it would help to know what data type we are dealing with here.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[breezett93]

Thanks for the replies!

That column is all numbers.

The width of the field is three. So instead of _23, I get 023.

 

[Olaf Doschke]

The field type is not about what you see, it's about the table schema column type definitions you see by inspecting the table in the table designer.
Anyway, since you say the width is 3 it should be char(3), but if that's also just by visual inspection of the data, you might not tell the real field width.

Just to make sure, please go into the table designer. Either you have the table in a project in the data tab, then select it and click "Modify" on the project manager window or you have it in the datasession window, then go via Properities dialog to Modify.

In both cases in the table designer look at Type and Width of the field.

Bye, Olaf.