How to remove/trim special characters in flatfile source field? - Consolidated Info

Que. How to remove special characters like ## in the below ... 
Can any one suggest... 

Prod_Code 
--- 
#PC97## 
#PC98## 
#PC99## 
#PC125# 
#PC156# 
--- 
--- 
--- 
#PC767# 
#PC766# 
#PC921# 
#PC1020 
#PC1071 
#PC1092 
#PC1221 


i want to remove that special characters.... 
i want to load in the target just 

Prod_Code 
--- 
PC9 
PC98 
PC99 
PC125 
PC156 .

Ans:
In expression ,use the replacechar function and in that just replace # with null char.

REPLACECHR

Availability: Designer Workflow Manager

Replaces characters in a string with a single character or no character. REPLACECHR searches the input string for the characters you specify and replaces all occurrences of all characters with the new character you specify.

Syntax

REPLACECHR( CaseFlag, InputString, OldCharSet, NewChar )

Argument	Required/ Optional	Description
CaseFlag	Required	Must be an integer. Determines whether the arguments in this function are case sensitive. You can enter any valid transformation expression. When CaseFlag is a number other than 0, the function is case sensitive. When CaseFlag is a null value or 0, the function is not case sensitive.
InputString	Required	Must be a character string. Passes the string you want to search. You can enter any valid transformation expression. If you pass a numeric value, the function converts it to a character string. If InputString is NULL, REPLACECHR returns NULL.
OldCharSet	Required	Must be a character string. The characters you want to replace. You can enter one or more characters. You can enter any valid transformation expression. You can also enter a text literal enclosed within single quotation marks, for example, 'abc'. If you pass a numeric value, the function converts it to a character string. If OldCharSet is NULL or empty, REPLACECHR returns InputString.
NewChar	Required	Must be a character string. You can enter one character, an empty string, or NULL. You can enter any valid transformation expression. If NewChar is NULL or empty, REPLACECHR removes all occurrences of all characters in OldCharSet in InputString. If NewChar contains more than one character, REPLACECHR uses the first character to replace OldCharSet.

Return Value

String.

Empty string if REPLACECHR removes all characters in InputString.

NULL if InputString is NULL.

InputString if OldCharSet is NULL or empty.

Examples

The following expression removes the double quotes from web log data for each row in the WEBLOG port:

REPLACECHR( 0, WEBLOG, '"', NULL )

WEBLOG	RETURN VALUE
"GET /news/index.html HTTP/1.1"	GET /news/index.html HTTP/1.1
"GET /companyinfo/index.html HTTP/1.1"	GET /companyinfo/index.html HTTP/1.1
GET /companyinfo/index.html HTTP/1.1	GET /companyinfo/index.html HTTP/1.1
NULL	NULL

The following expression removes multiple characters for each row in the WEBLOG port:

REPLACECHR ( 1, WEBLOG, ']["', NULL )

WEBLOG	RETURN VALUE
[29/Oct/2001:14:13:50 -0700]	29/Oct/2001:14:13:50 -0700
[31/Oct/2000:19:45:46 -0700] "GET /news/index.html HTTP/1.1"	31/Oct/2000:19:45:46 -0700 GET /news/index.html HTTP/1.1
[01/Nov/2000:10:51:31 -0700] "GET /news/index.html HTTP/1.1"	01/Nov/2000:10:51:31 -0700 GET /news/index.html HTTP/1.1
NULL	NULL

The following expression changes part of the value of the customer code for each row in the CUSTOMER_CODE port:

REPLACECHR ( 1, CUSTOMER_CODE, 'A', 'M' )

CUSTOMER_CODE	RETURN VALUE
ABA	MBM
abA	abM
BBC	BBC
ACC	MCC
NULL	NULL

The following expression changes part of the value of the customer code for each row in the CUSTOMER_CODE port:

REPLACECHR ( 0, CUSTOMER_CODE, 'A', 'M' )

CUSTOMER_CODE	RETURN VALUE
ABA	MBM
abA	MbM
BBC	BBC
ACC	MCC

The following expression changes part of the value of the customer code for each row in the CUSTOMER_CODE port:

REPLACECHR ( 1, CUSTOMER_CODE, 'A', NULL )

CUSTOMER_CODE	RETURN VALUE
ABA	B
BBC	BBC
ACC	CC
AAA	[empty string]
aaa	aaa
NULL	NULL

The following expression removes multiple numbers for each row in the INPUT port:

REPLACECHR ( 1, INPUT, '14', NULL )

INPUT	RETURN VALUE
12345	235
4141	NULL
111115	5
NULL	NULL

When you want to use a single quote (') in either OldCharSet or NewChar, you must use the CHR function. The single quote is the only character that cannot be used inside a string literal.

The following expression removes multiple characters, including the single quote, for each row in the INPUT port:

REPLACECHR (1, INPUT, CHR(39), NULL )

INPUT	RETURN VALUE
'Tom Smith' 'Laura Jones'	Tom Smith Laura Jones
Tom's	Toms
NULL	NULL