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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.
those un-wanted characters may be all over places, I checked STRREP but I don't think length would help. any idea?This message has been edited. Last edited by: nox,
You can define a custom function or simply use REPLACE But since it seems that you have a bunch of character to replace, may be complex because you will have to provide all special characters to replace
MartinY thank you for your information. I did try out using SQL function to do this and it work well, but do wish IBI can have this feature on it own, so don't have too rely on others.
I cannot tell you But personally, I would say no. I don't think that IBI will try to implement all possible function/feature that SQL can do. Having the ability to perform SQL passthrough is the answer to be able to accomplish almost anything from SQL : especially that depending on which SQL you connect to, the functions differs.
You will need to open a case and ask the question. It's only from there that you will get the right answer.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
Posts: 2410 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013
Here is a C user written function: first, copy the following code into nospchar.c:
#include <stdio.h>
#include <string.h>
#include <memory.h>
const unsigned char nospasc[256] = {
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,
0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,
1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
};
/*-------------------------------------------------------------------*/
/* xnospchar: return input text without special characters */
/*-------------------------------------------------------------------*/
/* */
int nospchar(unsigned char *input, double *dlen, unsigned char *output)
{
int len = (int)*dlen ;
unsigned char *in,*out,*end ;
if(len>256 || len<0) len = 0;
end = input + len;
memset(output,' ',len); /* blank out output */
for(in=input,out=output; in<end; in++) {
if(nospasc[*in]) *out++ = *in;
}
return(0);
}
/* this commented out main will printout the nospchar text from the
command line.
int main(int argc, char **argv)
{
unsigned char outc[256];
int len ;
double dlen;
memset(outc,0,sizeof(outc));
printf("argc: %d %s\n",argc,argv[1]);
if(argc != 2) { printf("usage: nospchar <string>\n"); return(0); }
len = strlen(argv[1]);
dlen = len ;
nospchar((unsigned char *)argv[1],&dlen,outc);
printf("%s\n",outc);
return(0);
}
*/
/* this commented out main prints out the nospasc array above for
upper and lower case latin1 characters
int main(int argc, char **argv)
{
unsigned char lower[26] = "abcdefghijklmnopqrstuvwxyz" ;
unsigned char upper[26] = "ABCDEFGHIJKLMNOPQRSTUVWXYZ" ;
int i,j;
memset(nospasc,0,sizeof(nospasc));
for(i=0; i<26; i++) nospasc[lower[i]] = 1;
for(i=0; i<26; i++) nospasc[upper[i]] = 1;
for(i=0; i<16; i++) {
for(j=0; j<16; j++) {
printf("%d,",nospasc[i*16+j]);
}
printf("\n");
}
return(0);
} */
Now, unfortunately you must read the documentation for your platform on how to make user functions. Usually, the IBI provided gencpgm.sh will create a compiled and linked program. For instance, under linux you can issue:
gencpgm.sh -g -e -v -m cpgm nospchar.c
Remember to issue the export IBICPG environment variable:
export IBICPG= [directory of user functions]
e.g. export IBICPG=/mypath/usrfuncpath
Here is a focexec that tests nospchar:
DEFINE FILE CAR
MYC/A15 = EDIT(COUNTRY,'9%lh9+*999999$9') ;
MYD/A15 = NOSPCHAR(MYC,15,MYD) ;
END
TABLE FILE CAR
PRINT COUNTRY MYC MYD
END
-RUN
So, we execute it:
EX TEST
To get:
PAGE 1
COUNTRY MYC MYD
------- --- ---
ENGLAND E%lhN+*GLAND ElhNGLAND
JAPAN J%lhA+*PAN JlhAPAN
ITALY I%lhT+*ALY IlhTALY
W GERMANY W%lh +*GERMAN WlhGERMAN
FRANCE F%lhR+*ANCE FlhRANCE
This message has been edited. Last edited by: Edward Wolfgram,