Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    How to strip out null values from a character field?
Go
New
Search
Notify
Tools
Reply
  
How to strip out null values from a character field?
 Login/Join
 
Member
posted
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
 
Posts: 4 | Registered: September 28, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
I think this may help.

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


7.6.6 Mainframe
7.6.4 Web Focus
Windows

 
Posts: 45 | Location: Gaffney SC | Registered: March 30, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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.
 
Posts: 4 | Registered: September 28, 2007Reply With QuoteReport This Post
Member
posted Hide Post
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.
 
Posts: 4 | Registered: September 28, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
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

 
Posts: 45 | Location: Gaffney SC | Registered: March 30, 2007Reply With QuoteReport This Post
Master
posted Hide Post
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
 
Posts: 755 | Location: TX | Registered: September 25, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 126 | Registered: January 18, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 210 | Location: Ottawa | Registered: November 03, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
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.06 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5604 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  iWay Software Product Forum on Focal Point    How to strip out null values from a character field?

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.