I am trying to extract only the numbers from an alphanumeric field that contains both numbers and letters. The field is a room number (ie. 1305B) and I need to separate out the numbers and letters. I am trying to do this via a define field and split the field into 2 new fields (one number and one letter), but I'm having trouble find a function or a combo of functions that will do the trick.

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

Try this:

APP FILEDEF TESTMAS DISK testdata.mas
-RUN
-WRITE TESTMAS FILENAME=TESTDATA,SUFFIX=FIX
-WRITE TESTMAS SEGNAME=TESTDATA,SEGTYPE=S0
-WRITE TESTMAS FIELDNAME=TESTFLD ,ALIAS=TESTFLD ,FORMAT=A12 ,ACTUAL=A12 ,$
-*
APP FILEDEF TESTDATA DISK testdata.ftm
-RUN
-WRITE TESTDATA A 127366250
-WRITE TESTDATA 2B27364433
-WRITE TESTDATA 12C456789
-WRITE TESTDATA 800969INF0
-WRITE TESTDATA 1012D2898
-WRITE TESTDATA 10121E
-WRITE TESTDATA 2 Penn Plaza
-WRITE TESTDATA 917339F380
-WRITE TESTDATA 9173394G50
-WRITE TESTDATA 21273662H0
-WRITE TESTDATA 212736443I
-WRITE TESTDATA 2127366250
-WRITE TESTDATA 10121 J
-RUN
-*
DEFINE FILE TESTDATA
 PATTERNX/A12 = PATTERN (12,UPCASE(12,TESTFLD,'A12'),'A12');
 REPLACEA/A12 = STRREP (12,PATTERNX,1,'A',1,'$',12,'A12');
 REPLACEB/A12 = STRREP (12,REPLACEA,1,' ',1,'$',12,'A12');
 NUMBERS/A12  = EDIT(TESTFLD,REPLACEB);
 REPLACE1/A12 = STRREP (12,PATTERNX,1,'9',1,'$',12,'A12');
 REPLACE2/A12 = STRREP (12,REPLACE1,1,'A',1,'9',12,'A12');
 REPLACE3/A12 = STRREP (12,REPLACE2,1,' ',1,'9',12,'A12');
 LETTERS/A12  = EDIT(TESTFLD,REPLACE3);
END
-*
TABLE FILE TESTDATA
 PRINT TESTFLD NUMBERS LETTERS
END

Awesome code, well done!

Thanks Waz. I was both lucky and happy to discover that the EDIT function accepts a field as input for the 'mask' argument. This fact allows the combination of PATTERN and EDIT functions to provide a dandy solution to this type of problem.

Just wondering, anyone know what release PATTERN became available? We are on 7.1.3 and it is not part of our release.

Looks like it was 7.6.5. New Features - release 7.6.5

It works in 7.6.4 as well, and is documented in the Using Functions Manual Version 7 Release 6

If your version of FOCUS/WebFOCUS doesn't support function PATTERN, here is an alternative approach using the SUBSTR and CHKFMT functions. The -REPEAT loops are based on the length of the character string being tested (12 in this case), so make appropriate changes for the length of the string you are trying to split.

APP FILEDEF TESTMAS DISK testdata.mas
-RUN
-WRITE TESTMAS FILENAME=TESTDATA,SUFFIX=FIX
-WRITE TESTMAS SEGNAME=TESTDATA,SEGTYPE=S0
-WRITE TESTMAS FIELDNAME=TESTFLD ,ALIAS=TESTFLD ,FORMAT=A12 ,ACTUAL=A12 ,$
-*
APP FILEDEF TESTDATA DISK testdata.ftm
-RUN
-WRITE TESTDATA A 127366250
-WRITE TESTDATA 2B27364433
-WRITE TESTDATA 12C456789
-WRITE TESTDATA 800969INF0
-WRITE TESTDATA 1012D2898
-WRITE TESTDATA 10121E
-WRITE TESTDATA 2 Penn Plaza
-WRITE TESTDATA 917339F380
-WRITE TESTDATA 9173394G50
-WRITE TESTDATA 21273662H0
-WRITE TESTDATA 212736443I
-WRITE TESTDATA 2127366250
-WRITE TESTDATA 10121 J
-RUN
-*
DEFINE FILE TESTDATA
-REPEAT ENDREPEAT1 FOR &I FROM 1 TO 12
 TESTCHAR/A1  = SUBSTR(12,TESTFLD,&I,&I,1,'A1');
 CHECKFMT/I1  = CHKFMT(1,TESTCHAR,'9','I1');
 NUMBERS&I/A1 = IF (CHECKFMT EQ 0) THEN TESTCHAR ELSE '';
 LETTERS&I/A1 = IF (CHECKFMT NE 0) THEN TESTCHAR ELSE '';
-ENDREPEAT1
-*
 NUMBERS/A12 = NUMBERS1
-REPEAT ENDREPEAT2 FOR &I FROM 2 TO 12
 || NUMBERS&I
-ENDREPEAT2
 ;
-*
 LETTERS/A12 = LETTERS1
-REPEAT ENDREPEAT3 FOR &I FROM 2 TO 12
 || LETTERS&I
-ENDREPEAT3
 ;
END
-*
TABLE FILE TESTDATA
 PRINT TESTFLD NUMBERS LETTERS
END

This message has been edited. Last edited by: Dan Satchell,