SSIS REPLACE Double Quote

Question

SSIS REPLACE Double Quote

Ed Zann

SSCrazy Eights

Points: 8694
More actions
February 27, 2007 at 2:36 pm

#123128

I was trying to use the Replace function in the Derived Column transform to strip double quotes (abbrev for inches as in 6'4") out of a data field, but couldn't get the syntax right. Here is what I tried unsuccessfully:
REPLACE([AssetDescr], ""","in")
Does anyone have a syntax that works here?
As a workaround, I modified the SQL query in the OLEDB Source with the SQL REPLACE function, but I was just wondering if it can be done via the Derived Column.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

Haven't tried this in SSIS, but it looks like you have 3 double quotes in a row. Try to enclose it search value with single quotes?

REPLACE

([AssetDescr], '"','in')

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 2

I already tried that but it also failed. In the Derived Column transform, even though the commands appear to be SQL, apparently they are not. I have found that string values must be enclosed in double quotes.

Leonce SSC Enthusiast Points: 198 More actions · Answer 3

Leonce

SSC Enthusiast

Points: 198

February 28, 2007 at 3:14 pm

#692446

Try REPLACE([AssetDescr], "\"","in")

Leonce

Bob Snyder Right there with Babe Points: 727 More actions · Answer 4

Within TSQL you could try - but with your column name instead of the local variable of course.

declare @AssetDescr VARCHAR(30)

set @AssetDescr= 'This has quotes at 6".'

select @AssetDescr

SELECT REPLACE(@AssetDescr, '"','in')

Glen Whitling SSC Journeyman Points: 85 More actions · Answer 5

The use of an SSIS variable can be used for the " character and that will allow the replace to function correctly.

Glen

srinione Valued Member Points: 59 More actions · Answer 6

srinione

Valued Member

Points: 59

May 14, 2008 at 9:07 am

#815280

Thanks, it worked.

joshi_druva SSC Journeyman Points: 81 More actions · Answer 7

joshi_druva

SSC Journeyman

Points: 81

February 12, 2009 at 7:04 am

#942819

Thanks it worked.

Thanks a lot

silpa001 SSC-Addicted Points: 471 More actions · Answer 8

Can anybody suggest me how to use the replace function in derived column transformation.

I have a expression REPLACE(UPPER(NAME),@[User::Quote]," ") but this is not replacing quote in destination.

jt_15 Valued Member Points: 60 More actions · Answer 9

Use Leonce suggestion above "... REPLACE([AssetDescr], "\"","in")" it works.

munawargani2001 SSC Journeyman Points: 95 More actions · Answer 10

Check out this link. It is just one step quick way.

http://www.mssqltips.com/tip.asp?tip=1316

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 11

munawargani2001 (1/14/2011)

Check out this link. It is just one step quick way.

http://www.mssqltips.com/tip.asp?tip=1316%5B/quote%5D

This will work if the data comes out of a flat file.

However, no one in this thread has mentioned anything about flat files.

Furthermore, the original question mentions an OLE DB Source, so the data comes out of a database.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

sjohnson022 Valued Member Points: 53 More actions · Answer 12

Thanks for the post, this really helped me. the variable is the only way to handle this situation.

peterzeke SSCrazy Eights Points: 9003 More actions · Answer 13

sjohnson022 (9/9/2011)
Thanks for the post, this really helped me. the variable is the only way to handle this situation.

Could you post what your replace expression looks like with the variable in use? I'm having trouble visualizing the solution.

kramaswamy SSCoach Points: 18181 More actions · Answer 14

What's your question peterzeke? This thread is very old, with several different questions and answers within.