SQL Server Performance Forum – Threads Archive

SQL Query to replace the contents of a column

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 ?
Regards,
Chandra

---

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.

---

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,
Bhushan
http://www.metacafe.com/watch/365213/wonder_what_you_are
Edited: Added SQL task info in the alternate way.

---

]]>