question

manuu avatar image
manuu 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?
sql-server-2008sql-server-2005ssissql-server-2008-r2sql-server
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks @grant and @pavel I need to convert it for SSIS. So I want to give formula which SSIS support....
0 Likes 0 ·
Updated the answer to cover also the SSIS expression.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks @pavel.. It worked perfectly........ :-)
0 Likes 0 ·
If @Pavel has the answer, be sure to vote it up and mark it.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.