Focal Point Banner


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.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]To keep only alphabet and remove rest of character(include space)

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED]To keep only alphabet and remove rest of character(include space)
 Login/Join
 
Platinum Member
posted
Hi,

Let said I have below field value:

NO. | Description
1 | help34to**solve proble(m
2 | ho23w yo44u do99ing toda y?
3 | &th@is is (very) n iceay

I want to turn into this:

NO. | Description_2
1 | helptosolveproblem
2 | howyoudoingtoday
3 | thisisveryniceday

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,


WebFOCUS v8.2.06 , Windows
 
Posts: 137 | Registered: August 29, 2019Report This Post
Virtuoso
posted Hide Post
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

DEFINE FILE abc
newField /A250 = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE (oldField, '1', ''), '2', ''), '3', ''), '4', ''), '*', ''), '(', ''), ')', '');
END


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, 2013Report This Post
Platinum Member
posted Hide Post
Thanks MartinY, this is an idea, but is there a better way to extract only 'A-Z' and 'a-z' only?


WebFOCUS v8.2.06 , Windows
 
Posts: 137 | Registered: August 29, 2019Report This Post
Virtuoso
posted Hide Post
Out of the box IBI functions are to remove/replace characters not to keep desired ones.

Maybe you can accomplish that using SQL passthrough


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, 2013Report This Post
Platinum Member
posted Hide Post
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.


WebFOCUS v8.2.06 , Windows
 
Posts: 137 | Registered: August 29, 2019Report This Post
Virtuoso
posted Hide Post
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, 2013Report This Post
Expert
posted Hide Post
Not sure if it would help but look at the REGEX function - link

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 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Gold member
posted Hide Post
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,


IBI Development
 
Posts: 61 | Registered: November 15, 2005Report This Post
Master
posted Hide Post
quote:
...but do wish IBI can have this feature on it own, so don't have too rely on others.

The WF function, DB_EXPR, will allow you to use native SQL functionality, within the WF reporting language.

Example running against the WF Retail Lite MS SQL Server database:
DEFINE FILE WF_RETAIL_STORE
 AREARANGESTFT/A20V = DB_EXPR(REPLACE(TRANSLATE("AREA_RANGE_SQ_FT", '0123456789 .-:,', '@@@@@@@@@@@@@@@'), '@', '') );
 AREARANGESTM/A20V  = DB_EXPR(REPLACE(TRANSLATE("AREA_RANGE_SQ_M",  '0123456789 .-:,', '@@@@@@@@@@@@@@@'), '@', '') );
END
-*
TABLE FILE WF_RETAIL_STORE
"'Keep' Only 'A-Z' Characters In a String."
PRINT AREA_RANGE_SQ_FT
      AREA_RANGE_SQ_M
      AREARANGESTFT
      AREARANGESTM
-*
ON TABLE SET STYLE *
  INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/jellybean_combo.sty, $
  TYPE=DATA, JUSTIFY=CENTER,$
 ENDSTYLE
-*
IF READLIMIT EQ 10
IF AREA_RANGE_SQ_FT IS-NOT MISSING
END 




In the SQL trace you can see the DB_EXPR value, within the generated SQL:

SELECT
TOP 10 (REPLACE(TRANSLATE(T1."AREA_RANGE_SQ_FT", '0123456789
.-:,', '@@@@@@@@@@@@@@@'), '@', '') ),
(REPLACE(TRANSLATE(T1."AREA_RANGE_SQ_M",  '0123456789 .-:,',
'@@@@@@@@@@@@@@@'), '@', '') ),
T1."AREA_RANGE_SQ_FT",
T1."AREA_RANGE_SQ_M"
FROM
wrd_wf_retail_store T1
WHERE
(T1."AREA_RANGE_SQ_FT" IS NOT NULL); 

This message has been edited. Last edited by: David Briars,




Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
 
Posts: 822 | Registered: April 23, 2003Report This Post
Member
posted Hide Post
Hi,
didn't thinks EDIT could receive a field as second argument but it does , funny

DEFINE FILE CAR
T1/A100 WITH CAR = IF CAR EQ 'AUDI' THEN 'HEPto34**TOSOLCE'
ELSE IF CAR EQ 'BMW' THEN 'HDW FFF ? DDD DD' ELSE 'HEPto34';
P1/A100 = PATTERN(100,T1,'A100');
R1/A100 = STRREP(100,P1,1,'9',1,'$',100,R1);
R2/A100 = STRREP(100,R1,1,'A',1,'9',100,R2);
R2/A100 = STRREP(100,R2,1,'a',1,'9',100,R2);
R2/A100 = STRREP(100,R2,1,'*',1,'$',100,R2);
R2/A100 = STRREP(100,R2,1,' ',1,'$',100,R2);
R2/A100 = STRREP(100,R2,1,'?',1,'$',100,R2);
F1/A100 = EDIT(T1,R2);
END

TABLE FILE CAR
PRINT T1 P1 R1 R2 F1
CAR
END


Damien
WF 8202
DB : NETEZZA, MSSQL, DB2, ORACLE
Output : ALL
 
Posts: 11 | Location: Belgium- Luxembourg | Registered: May 08, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED]To keep only alphabet and remove rest of character(include space)

Copyright © 1996-2020 Information Builders