Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

(2 Ratings)
facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Remove special characters using a PowerCenter expression
Solution

To remove special characters from a text string, use the REG expression. This allows the user to retain only those characters that are required in a target object.

 

Following is the syntax:

REG_REPLACE(PORT_NAME, '[^\w,-. ]', '')

This function removes the special characters and retains only alphanumeric characters, commas, dashes, and periods.

 

The following example retains alphanumeric characters only:

REG_REPLACE(PORT_NAME,'[^a-z0-9A-Z]','')

Note

Informatica supports PERL compatible REG expression syntax.

More Information

The above examples are a much simpler alternatives to using the REPLACESTR function to remove special characters as seen from the following expression:

(REPLACESTR(1,SUBSTR(
  REPLACECHR(0,
    REPLACECHR(0,
 REPLACECHR(0,CMMT_HD_DSCR,CHR(13),CHR(32)),
    CHR(10),CHR(32)),
  '|',CHR(32)),
0,50),CHR(194),CHR(172),CHR(195),CHR(191),CHR(063),CHR(131),CHR(239),CHR(189),CHR(162),CHR(165),CHR(168),CHR(188),CHR(169),CHR(167),CHR(170),CHR(174),CHR(224),CHR(177),CHR(208),CHR(236),CHR(181),CHR(243),CHR(179),CHR(166),CHR(230),CHR(063),CHR(214),CHR(246),null))
Additionally, if you use the REPLACESTR and CHR functions, add another CHR function whenever you come across a new special character with this function set.
Reference
Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:12/10/2014 9:44 PMID:139362
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)