September 13, 2011 at 12:26 pm
Use Replace
REPLACE( @[System::MachineName] , "'", "''" )
September 3, 2013 at 7:32 am
Hello All,
Im in desperate need. Please help.
Im loading a csv file using SSIS. I have a column with | embedded in it. And this column varies in lenth and so does the occurence of | delimiter. Its only in some of my rows does a particular string appears and I need to append this string to the next column.
Im trying to use the Derived Column transformation with REPLACE function but in vain. Can someone suggest where im going wrong.
Here is my sample data.
transit|Name|edcytd|Missing, defect
Needs to be tranformed as
transit|Name|edcytd,Missing-defect
I know this is a two step process. I can use concatenate function Expr1+"-"+Expr2 to merge the two strings. How do I seperatethe needed string from the original?
Thanks
Pallavi
September 3, 2013 at 7:37 am
Don't you have a text qualifier defined?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 3, 2013 at 8:04 am
I tried that but really did not see any difference in how the data is read.Also, this delimitter is only in one column and I need them to be present in the table as well.Its only the occasional last part of the string to be appended to the next column.
I also read a post on adding text qualifier. I understood that it helps removes the unwanted special characters in the beginning or at the end of the column. Please correct me if wrong.
Thanks
Pallavi
September 3, 2013 at 12:42 pm
If this is your input data
transit|Name|edcytd|Missing, defect
you'll need to add text qualifiers to the input like this:
"transit"|"Name"|"edcytd|Missing, defect"
That way, you'll be able to distinguish the last | as part of the data, instead of a delimiter.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply