 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Focal Point 
Focal Point Forums iWay Software Product Forum on Focal Point How to strip out null values from a character field?
Focal Point Forums iWay Software Product Forum on Focal Point How to strip out null values from a character field? Read-Only TopicGo | Search | Notify | Tools |
| How to strip out null values from a character field? | Login/Join |
| Member |
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 | ||
|
| Silver Member |
I think this may help. http://documentation.informationbuilders.com/masterinde...13snf/rep_lang21.htm 7.6.6 Mainframe 7.6.4 Web Focus Windows | |||
|
| Platinum Member |
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 | |||
|
| Member |
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. | |||
|
| Member |
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. | |||
|
| Silver Member |
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 | |||
|
| Master |
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 | |||
|
| Platinum Member |
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 | |||
|
| Platinum Member |
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 | |||
|
| Expert |
Morgan, Don't forget (how can we  ) 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  .T
| |||||||||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums iWay Software Product Forum on Focal Point How to strip out null values from a character field?
Focal Point Forums iWay Software Product Forum on Focal Point How to strip out null values from a character field?Copyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |