SSIS Non numeric values caused by text fields being parsed out

1

How can I parse out non-numeric values using a stored procedure? I have a table that I replicate in an SSIS package. Unfortunately, when this table sends to the gpg output it includes values
like:, |, , .

Example - this is what the result set looks like for my Title column:

FLASH| BANG| POP| FIZZ|: E-OP
YES, YOU CAN|||: POSITIVE -OP

and I need it to be:

FLASH BANG POP FIZZ E-OP
YES, YOU CAN POSITIVE -OP
Is there a way to remove these unwanted values using a stored procedure? I tried the Derived column but have never used it and it keeps throwing errors.
I appreciate your help!

2 
DECLARE @tbl table
   (
      some_string nvarchar(4000)
   );
INSERT
   @tbl
VALUES
   (
      'FLASH| BANG| POP| FIZZ|: E-OP'
   )
 , (
      'YES, YOU CAN|||: POSITIVE -OP'
   );
SELECT some_string FROM @tbl;
SELECT
   Replace(Replace(some_string, '|', ''), ':', '') cleaned_up
FROM
   @tbl;

image

3

Thank you so very much!!!

4

How would I do this if I wanted the nested replace to check 2 columns of data?
For example, I have a table with twenty columns and 4 million plus records. 2 of these columns (Title, Description) in the table have garbage like this 'YES, YOU CAN|||: POSITIVE -OP'. How can this be done to create a new table by selecting all the fields from a table and only replace replace the two columns Title and Description? Thanks for your help.

5 
  I think this will work.  Thanks for helping out!

  SELECT        ISBN, SKU_Number, Sku_Text, EAN, SKU_Type, REPLACE(REPLACE([Title],'|', ''), ':', '') AS Title, 
  REPLACE(REPLACE([Description],'|', ''), ':', '') AS Description, Author, Returnable, Status, PubCode, Publisher, 
  Vendor_Number, Vendors_Number, Manuf_Number, Manufs_Number, MfgItemNo, Repl_Code, 
                     Module, Dept, SDept, Class, SClass, Category, Dept_Name, SDept_Name, Class_Name, 
  SClass_Name, 
  Buyer_Number, BuyerName, Merch_Group_Number, Coordinate_Group, Manuf_List_Price, Home_Cost, 
                     Vendor_Cost, POS_Price, Initial_Home_Cost, SuspendCode, VoidFlag, Disposition, ILevel, Level_Ind, 
  Min_Qty, Max_Qty, Load_Date, Condition
 into tmp_load.dbo.test
 FROM            Reference.dbo.Item_Master