-1

I have a description field with special characters and unwanted number, I want to remove that special characters and unwanted numbers which is BOLD in the example description in report studio report.

Description Field:- 31- Customer Reference Number: 2388094412 ~32- BUYER NAME OR DEPARTMENT:F13 -~33- DELIVERY REQUESTED ON 06/01/11 ~34- CANCEL AFTER 06/16/15 ~35-ATTENTION ELAINE HOWIE~36- CANCEL ALL BACKORDERS. CHECK COSTS ******||** BEFORE SHIPPING ~37- FAX 43575 8795 ~10- CALL FOR APPT: 450/642-0101. ~30-**GST/HST #10574 8278 RT0001##~**

Note:- We don’t know how many special characters , unwanted numbers and specific length of characters. Cognos :-10.2.2 version Database :- Oracle Relational database

Thanks RK

Rk m
  • 1
  • 1
  • 1

1 Answers1

0

Instead of trying to define special characters to remove, I'd simply create a regular expression that defined the legal characters I'd want to include. Since you are using Oracle as your data source you can use the REGEXP_REPLACE Oracle native function to replace the characters that aren't in a specified list with an empty string.

Example:

REGEXP_REPLACE(description,'[^A-Za-z0-9 !@#$%^&*]','')

This would return the string with anything that wasn't a letter, number or specified punctuation removed. In practice you'd want to expand the list of punctuation to include more characters such as period and comma but I think this illustrates the concept.

Johnsonium
  • 1,985
  • 1
  • 10
  • 15