Trim a numeric field using update

[mssbass]

I have a numeric field called ups_active I need to cut leading and trailing blanks from; however, my SQL isn't working:

UPDATE r00_test SET up TRIM(STR (UPS_s_active =ACTIVE))

What am I doing wrong?

 

[Stella740pl]

To cut both, leading and trailing blanks, you should use ALLTRIM(). TRIM() is identical to RTRIM(), so it cuts off only right (trailing) spaces, which are not likely to happen when you use STR() on a numeric value. I guess, LTRIM() would be enough in your case. Is that what you mean under "not working"?

 

[Stella740pl]

Also, shouldn't it be

UPDATE r00_test SET up=TRIM(STR (UPS_s_active =ACTIVE))

I assume, UP is your field name and it is character string, and r00_test is the table name?

 

[mssbass]

The error is "data type mismatch" - the table is r00_test and the fieldname is ups_active

UPDATE r00_test SET ups_active = TRIM(STR (UPS_active))

 

[Stella740pl]

You mean, you are updating numeric field with the same value, only converted to string? That's you type mismatch. Even if you turn it back to numeric with VAL(), the actual value isn't going to change.
UPDATE is supposed to be used for value change, not the size of the field. If you need to reduce the size of your numeric field, you should probably use ALTER TABLE.

 

[mssbass]

Well, the reason I need to update it is because it has leading blanks

 

[mssbass]

Now the error shows (function argument value, type or count is invalid)

UPDATE r00_test SET ups_active = TRIM(STR(val (UPS_active)))

 

[Stella740pl]

But it's numeric, isn't it? Numeric cannot have any "blanks". The actual value is the same whether the field is filled "to the capacity" or has a few places to spare. What is it you are actually trying to do?

 

[mssbass]

Well, what I did earlier is updated this field to a numeric 2 instead of numeric 10 and my values erased - maybe it was a glitch - I'll try again Thank you

 

[Stella740pl]

The statement your posted should look like this:

UPDATE r00_test SET ups_active = VAL(ALLTRIM(STR(UPS_active)))

But as I said, it wouldn't change the value. Can you explain, why do you need to do this?

 

[craigsboyd]

REPLACE ups_active WITH LTRIM(ups_active) ALL IN "r00_test"

http://www.sweetpotatosoftware.com

 

[Stella740pl]

Our posts crossed again. Your field was cut to N(2) because your first value in the field was a 2-digit number. If you trying to reduce the number of numeric positions to, say, 5 instead of 2, try padding the value:

UPDATE r00_test SET ups_active = VAL(PADL(ALLTRIM(STR(UPS_active)),5,'0')

But I would use ALTER TABLE instead.

If reducing the field size is not what are you trying to do, then what?

 

[Stella740pl]

Craig, the field is numeric.

 

[Stella740pl]

mssbass,

Please confirm that your field is numeric and not string containig numeric characters. If it is, what are you trying to do and what you mean by leading blanks and why do you want them out?

 

[Stella740pl]

msbass,

Can I ask you again, how it is going and what you are trying to do in general?

Stella