Focal Point
How to strip out null values from a character field?

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/1381057331/m/8921065682

June 04, 2008, 05:24 PM
Morgan
How to strip out null values from a character field?
I have data fields that contain Null values contained within the actual character content.

How do I strip out the bad fields? In one example I have "MU***OZ JONES". The astericks represent the non acceptable columns.

I would like to end up with "MUOZ JONES"

Thanks,
Morgan

I will try to upload a screen shot of how the data appears
June 05, 2008, 07:01 AM
Lucas
I think this may help.

http://documentation.informationbuilders.com/masterinde...13snf/rep_lang21.htm


7.6.6 Mainframe
7.6.4 Web Focus
Windows

June 05, 2008, 08:54 AM
Jessica Bottone
Question: In the example you gave: "MU***OZJONES" - was that field created from several fields that had been concatenated together and some of these fields happened to contain nulls, or was it always one field that contained, as you say, nulls? If it's the first thing, I'd convert these null values to spaces prior to the concatenation and use strong concatenation. If it's the second thing, are they actually nulls or are they spaces? If they are spaces, you can use the SQUEEZE function to reduce multiple spaces down to a single space, but that will still leave you with one space. The TRIM function will get rid of leading and trailing spaces, but not the ones in the middle. If you never end up with more than a single space per field, you can use the GETTOK function to separate the field into two fields using the space as the delimiter, then use strong concatenation to bring them back together and get rid of the single space. I don't know of a real clean, elegant way to do this but maybe someone else does.

Good luck.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
June 05, 2008, 10:00 AM
Morgan
Thanks Jessica -

No it is not a field I concatenate it is how it is read off the file.

I will definately look into your other suggestions.
June 05, 2008, 10:04 AM
Morgan
Thanks Lucas -

I like the replace idea but I am not sure what the field would be to look for. I don't have asterisks in there - I have some some bizarre looking null like value.

Actually I believe in this case the name was originally munoz but on the mainframe iwas stored with the tilde above the "n"

So I am thinking the character is being garbled upon its transport to the wondows box or it is garbled on the mainframe but the mainframe is not affected by it.
June 05, 2008, 11:54 AM
Lucas
You could try a few different things

%20 is url encoding for a space.
& nbsp; //put that all together// is also a space...
you could also try ' '.....
maybe even something like \t for tab....

I am just guessing but hey you might get lucky and hit pay dirt with one of these.......


7.6.6 Mainframe
7.6.4 Web Focus
Windows

June 06, 2008, 08:41 AM
PBrightwell
See if you can find the record on the mainframe and turn hex on. That will tell you the actual configuration of the data. Then you can use CTRAN to translate it to a readable value.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
June 06, 2008, 02:25 PM
Jessica Bottone
If it really is still an uppercase N with a tilde on it and you just can't see it on the windows box, try this:

NEW_FIELD/AXX = CTRAN(XX, PUT_FIELD_NAME_HERE, 209, 78, 'AXX');

where XX is the lengh of the field and put your actual field name where I have PUT_YOUR_FIELD_NAME_HERE. The decimal representation for an uppercase tilde N is 209; 78 is for a regular N. If it's lowercase, use 241 instead of 209 and 110 instead of 78.

Good luck.


Data Migrator 5.3, 7.1, 7.6
WebFOCUS 7.1, 7.6, 7.7
SQL Server, Oracle, DB2
Windows
June 09, 2008, 12:40 PM
smiths
If you are unable to see the hex equivalent on the mainframe somehow as Pat suggests, use UFMT to see the hex equivalent of the characters. I have shown a simple example using Dialog Manager here:

-SET &VAR = '}xy';

-SET &LENGTH = &VAR.LENGTH;
-SET &FORMAT = 'A' | &LENGTH;
-SET &LNGU = &LENGTH * 2;
-SET &FMTU = 'A' | &LNGU;

-* UFMT(infield, inlength, {outfield|'format'})

-* Convert the field from alpha to hex (so that WF query
-* can handle occurrences of single-quote at end of string)
-SET &FIELDU = UFMT(&VAR , &VAR.LENGTH , '&FMTU');

-TYPE FIELDU: (&FIELDU)


Then you will know which character(s) you will need to run through CTRAN, as the others have suggested.

Regards,
Sean


------------------------------------------------------------------------
PROD: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
TEST: WebFOCUS 7.6.2 on Unix AIX/Tomcat/Servlet Mode
June 10, 2008, 03:49 AM
Tony A
Morgan,

Don't forget (how can we Frowner) that Mainframe and Windoze speak differently EBCDIC, ASCII, ANSI etc. and, depending on how you transport your file across from the mainframe there could be different settings that you can use to affect the resultant file receipt.

Also, please update your signature so that we know to what version and platform your question relates. You never know it might affect the recommendations given. Updating your signature instead of supplying the info each time also saves you some typing Smiler.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10