replace all values in a column with length equal to X 1

[gabydrdoom]

Hi!

How do I replace all values in a column with length equal to 10?

Ex:

2535848577
5487
535
66666
5366666655
758675

replacement only values with length equal to 10
Merci!

 

[GriffMG]

replace myfield with m.something for len(alltrim(transform(myfield)))=10

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Do you mean that you want to replace the field with a new value, but only when the length of the existing value is 10? If so, do what Griff said.

Or, do you want to replace the field with the same value, but expanded to ten character? And to do that for every record?

If so, do this:

REPLACE ALL MyField WITH PADL(MyField, 10)

That assumes that it is a character field, and that you want to pad the existing value with spaces to the left.

If none of the above applies, then please clarify your question.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SkipVought]

Do you mean that the end product should have 10 characters in each row: leading ZEROs for numeric values or trailing spaces for alphnumeric values?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Olaf Doschke]

I think all variations of what you could want are covered.

Now it will also depend on what field type you have, what's possible.
Edit: I just see now Griff is really clever and uses TRANSFORM() to convert any data type to string, to be able to TRIM it and find the length without whitespace. Specifically for length 10 this would also cover the date data type, which has 8 digits and two date marks = 10 characters in the usual CENTURY ON mode.

So in short, the question may come from seeing LEN(field) is constant. Yes, unless you have varchar or Memo fields, char fields are fixed size.

But then it's mainly the others, who tackled padding. Other ways to tackle this are alignment at display or printing. For numbers, right alignment is possible in controls easily, just use their Alignment property. In report controls you find everything in the Format tab. Well, you can also display text data types right-aligned, ie something like alphanumeric serial numbers, part-numbers, etc. No need to work on the data and store it right-aligned with leading spaces or zeros, which only works in monospaced/nonproportional fonts anyway.

Also, see Inputmask and Format properties of the textbox and similar on report controls. And how that corresponds to the legacy @..say PICTURE option.

I'd rarely store leading spaces or zeros in data. It's bound to fail. When the time comes you need an extension altering a field from C to C(n+1) you get a trailing space, not a leading one. And anyway, in char fields you have fixed length, just always trailing spaces, not leading spaces or zeros. Therefore for me, formatting is something you do after you read data at display/report/etc. Not within the database.

Special cases need special handling, for example, serial numbers might come in series, some with a prefix, some with a suffix. It will depend on the way you align them in the storage how an index can sort them so the ones belonging to a group are sorted together.

Obviously you can always do as you like, but take it for granted from experience, mixing semantics with the pure data makes it less easy to change things.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Olaf Doschke]

Also, when Mike and Skip have the right feeling about wanting to sort a number stored as a string by its value, you can index on val(field) for that.

Besides, you can store longer numbers in numeric fields in VFP, float (VFP uses double precision) would allow up to 15 digits integers and N(20), well 20 digits. And these fields then sort numerical with a simple index on the field.

So often you may not need a character data type even for longer numbers. It can be advisable for even shorter ones like postal codes (in Germany they're purely numeric 5 digit codes). Mainly just because such numbers are only involved in textual processing, including APIs for address verification etc.

So last not least I can understand why you wouldn't go for numerical data types just because the range still is sufficient. Character fields are easily merged into templates and don't cause any precision problems in conversions. Well, N(x) fields are actually special about this, as their binary storage format in a dbf, astonishingly, is as a string.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name