need to scan a table and replace field values ending with letters with the same value w/o letter 4

[titoneon]

Hello,
I have a table where, one of the field is named "draw_no", field length is 7 and it is character field. and the values could be like shown below.

118845
118845A
118845B
118845C
8845A
8845B
833X

I need to find the way to scan the whole table and replace the field values, the one with letters, to be w/o the letter, except those ending on "X"
Can anyone help me ?
Thanks a lot

 

[Mike Lewis]

TRANSFORM(draw_no, "9999999") does not make the column to remove the letter from the end but at the same time display the whole value in this field

Oh, yes, I see why. What I should have said is to replace it with [tt]TRANSFORM(VAL(draw_no), "9999999")[/tt]

Since your aim is to learn these "tricks"[tt]^*[/tt], let me explain. VAL() converts a string to a numeric, up to and excluding the first non-numeric character. So "118845A" will be converted to a numeric 118845, and "8346A" to 8346.

TRANSFORM() converts that numeric value back to a string. The second parameter, that is the "9999999", says that the result of the conversion will consist of a string seven characters wide. So, that's the result you want.

Now, the point is that, in a SQL SELECT statement, if a result field is based on an expression (as opposed to a field in the input table), then VFP has to determine how wide to make the field in the result set. It does that by looking at the width of the field in the first record of the result set. If other records contain larger values, they will get truncated. That shouldn't happen with my code, because the "9999999" forces all the values to the same width.

That said, Olaf was using PADR() to force the result set to a given width, which is also correct. I'm not sure why you were not getting the correct result with that code, which is why I said I wasn't sure if my solution was correct.

Hope this makes sense.

([tt]^*[/tt] They are not "tricks" to us, of course, but rather the tools of the trade.)

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[titoneon]

Thanks Mike, very well explained, i understood, i called Tricks but in reality it is a fact that are tools
Thanks again

 

[Olaf Doschke]

>for a reason if a value of the DRAW_NO is equal to "118845A" or "118845", i will get "118" and not "118845" or if the value is "8346A", i will get "8".

I see, that's because STR results in char(10) and PADR() and a short draw_no length would cut off numbers. Either use PADL instead of PADR or better TRANSFORM instead of STR:
IIF("X"$draw_no, draw_no, PADR(TRANSFORM(VAL(draw_no)),LEN(draw_no))) as draw_no

Bye, Olaf.