OK...I'm sure this is a very easy thing to do but I have no clue...I have a field that has 2 and 4 numbers in it. I need to trim it to only the 2 first numbers. In otherwords, I need to drop the last 2 numbers even though some only have 2 numbers in it in which case I don't want to do anything to those.

Thank you,
Ana

If it's a left-justified alpha field, you can do the following:

COMPUTE FIELD2/A2 = EDIT(FIELD1,'99$$');

or the following preferable method:

COMPUTE FIELD2/A2 = SUBSTR(4, FIELD1, 1, 2, 2, 'A2');

If it's a numeric field, you would first have to convert it to Integer (if it isn't already), and then use the EDIT or SUBSTR function:

COMPUTE FIELD1I/I4 = FIELD1;

Then

COMPUTE FIELD2/A2  = EDIT(FIELD1I,'$$99');

or:

COMPUTE FIELD2/A2  = SUBSTR(4, FIELD1I, 3, 4, 2, 'A2');

In the EDIT function, the mask character '9' means copy the corresponding character in the field, '$' means ignore.

Syntax for substring function:

SUBSTR(inlength, parent, start, end, sublength, outfield)

How about this:

COMPUTE or DEFINE
NUMFLD/I4=IF NUMFLD LT 100 THEN NUMFLD ELSE IF NUMFLD LT 1000 THEN NUMFLD/10 ELSE NUMFLD/1000;

If the field is not integer to start with, you can use an equality to make it so.

Thanks Francis..That did it!!! BUT I need it to be a define now that i think of it because once I do this I need to exclude all that equal 50. :-( The compute did strip it down to 2 but now I can't use it in my where clause.

This message has been edited. Last edited by: Latigresa,

You can also use these COMPUTE's in a define, but keep in mind that this will only cause effect on record level while COMPUTE works on aggregated data.

Do you only use this for your where statement? If so, post you're WHERE statement here.

I did a define using the same data you gave me Francis and it worked great!!!

Thanks A lot!!!

Don't forget that this kind of manipulation in a DEFINE on a dbms table will not generate efficient SQL.

I'm not using SQL thank goodness..

BTW, does Latigresa mean "The tigress"?

Yes it does...

Ana

Are you dangerous?

quote:

If it's a left-justified alpha field, you can do the following:

COMPUTE FIELD2/A2 = EDIT(FIELD1,'99$$');

or the following preferable method:

COMPUTE FIELD2/A2 = SUBSTR(4, FIELD1, 1, 2, 2, 'A2');

I would us EDIT over SUBSTR in most cases. EDIT is an Internal function built into WebFOCUS. SUBSTR is an External Function stored in an external library that must be accessed. The only time I use SUBSTR is if the string I want is very long.

Bucket of cold water for Francis!

Tony, good morning!

My preference is GinnyJakes for the sake of easier to maintain. Although the other recommendations are equally as nice.
Guess Latigresa is roaring now

Ira wf 5.3.6 aix533

Sorry...I just got logged in again and read all the messages...Not dangerous at all..lol... ...and YES I was ROARING and didn't want to stop till I got done with all of it...I'm done now for the most part...

THANKS to all of you that helped me out!!!

I was asking if you were dangerous because "Removing digits" sounded like you wanted to bite somebody's fingers off

Well usually by the time I place a question on here it is because I am extremely frustrated from not being able to get it to work on my own and that's probably why I use some of the words I do cause I am thinking bad thoughts. but tryingto place a question on here without letting everyone know at the same time. Hahaha...

Anyhows...

Merry Christmas and Happy New Year to all!!!