asked
Want to insert '&' symbol in data.
Hi All, I have a source table where one of column has data like: **Column** SD SD **..** **..** JK Jk **..** **..** I want to load this data into staging in format of: **Column** S&D S&D **..** **..** J&K J&k **..** **..** Meaning I want to insert '&' character whereever we have data like SD and JK, while loading to staging. I need to convert it for SSIS. So I wanr to give this here SSIS formula to achieve this. How can we achieve this?
As @Grant mentioned, use the STUFF function. In case it should be applied only in some cases eg. only for SD, JK then together with the CASE statement. eg. WITH Data AS ( SELECT 'SD' AS Col UNION ALL SELECT 'JK' AS Col UNION ALL SELECT 'DK' AS Col UNION ALL SELECT 'AKfds' AS Col UNION ALL SELECT 'fdsf' AS Col UNION ALL SELECT 'fdfd' AS Col ) SELECT *, CASE WHEN Col IN ('SD', 'JK') THEN STUFF(Col, 2, 0, '&') ELSE Col END AS NewCol FROM Data **UPDATE** In Case of SSIS you can do this directly in the query for the data source or use a Derived Column for this. In case of Derived Column the expression in Derived Column will look like: Col == "SD" || Col == "JK" ? LEFT(Col,1) + "&" + RIGHT(COl, LEN(Col) - 1) : Col Depending on the data type of the column, it is possible that you will have to add casts to appropriate data types to get it working
If @Pavel has the answer, be sure to vote it up and mark it.
You should take a look at the [STUFF function][1]. It will do exactly what you need. [1]:
http://technet.microsoft.com/en-us/library/ms188043.aspx
16 People are following this question.
