SQL Server Performance

SQL Query to replace the contents of a column

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by chandra_g04, Jun 6, 2007.

  1. chandra_g04 New Member

    Here i submitted a sql query to replace the contents inside the brackets

    REPLACE('ind ind gem ind ind', substring('ind ind gem ind ind', charindex('(', 'ind ind gem ind ind', 1), charindex(')', 'ind ind gem ind ind', 1) - charindex('(', 'ind ind gem ind ind', 1) + 1), '')

    But I need to replace the contents inside the brackets of a column retrieved as a dataset during Data Flow Task in SSIS.

    Is there any way ?

  2. Adriaan New Member

    REPLACE only shows the result of the whole replacing. If you want to update a column by replacing, you need to use:

    UPDATE mytable SET mycolumn = REPLACE(.............)
    WHERE .............

    I have no idea how this integrates in SSIS, but that's the basic syntax.
  3. bhushank21 New Member

    Chandra, in your Data Flow Task, you can add a Script Component task which has the dataset as the input.
    In the Script Component properties, select the input column you want to transform.
    Add a corresponding new output column.
    Then in Design Script, write vb.net code to do the replacement.

    Use this new output column as the input for the data destination.

    Alternatively, if you are simply putting the entire dataset into a SQL table, you can use Adriaan's query which will do the data transformation at the end of all data transfer.
    This query should be used in a SQL Task in the Control Flow page and after the data flow task.

    Try using the OLEDB command task as well, I am not much familiar with this one.

    Thanks and Regards,

    Edited: Added SQL task info in the alternate way.

Share This Page