Derived column Replace function not working for double quotes

Question

Post reply

Derived column Replace function not working for double quotes

silpa001

SSC-Addicted

Points: 471
More actions
August 20, 2010 at 10:15 pm

#387628

Below is my code in derived column transformation expression to replace double quote with nothing, but this is not working, still i can see quotes after transfer. Can some body please suggest correct expression to replace double quotes ?
REPLACE(UPPER(NAME), "\"","")

Viewing 8 posts - 1 through 7 (of 7 total)

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

At first sight, I can't spot any errors either in your expression.

Are all the quotes still there, or are some of them replaced with empty strings?

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

David Dubois Right there with Babe Points: 763 More actions · Answer 2

Try:

REPLACE(UPPER(NAME), '"','')

Here's a working example:

declare @name varchar(30)

set @name = 'a"string"with"double"quotes'

set @name = REPLACE(UPPER(@NAME), '"','')

select @name

If you have quoted_identifier set off, you can use:

REPLACE(UPPER(NAME), """","")

set quoted_identifier off

declare @name varchar(30)

set @name = 'a"string"with"double"quotes'

set @name = REPLACE(UPPER(@NAME), """","")

select @name

I might also ask, do you intend to only remove double-quotes ("), or do you have a need also to remove single-quotes (')?

Cadavre SSC-Forever Points: 41690 More actions · Answer 3

If there are quotes around all of your data, try this.

Otherwise, try

REPLACE(NAME, "\"","")

If that works, then the problem is with "Upper", so you could set a derrived column that first converts the string to UPPER then strip the quotes from the derrived column.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

H4K Hall of Fame Points: 3374 More actions · Answer 4

Or you can also Try this....

Replace(NAME,chr(34),"")

Regards,

Amar Sale

BI Developer
SSRS, SSIS, SSAS, IBM Cognos, IBM Infosphere Cubing services, Crystal reports, IBM DB2, SQL Server, T-SQL
Please visit... ApplyBI

saileshk_nair SSC Rookie Points: 49 More actions · Answer 5

Hi All:

I have a text which is comming from a unicode DB2 database, and it contains a doublequote (").

I have a cleansing SSIS package with replace command to replace the unwanted characters from the input string before I import the data to my SQL database.

I am using a derived column in SSIS and its not identifying the doublequote character contains at my input string to replace as NOTHING.

I tried the same in SQL mangement studio and it works fine (remember here I have inserted double quote inside two single quote).

SELECT REPLACE(MYTEXT,'"', '') ---->> works fine

Derived column command (remember we need to use DOUBLE QUOTE in the derived column expression, so i have to use escape sequence)

REPLACE (MYTEXT,"\"", "") ---->> its not identifying the double quote contains at my text.

could someone please help me out with any idea? Is there something that I need to deal with ASCI codes?

Thanks,

Sailesh

sam.dahl Hall of Fame Points: 3112 More actions · Answer 6

I had a similar issue with space characters, turns out not all space characters are created equal.

You can take the approach described here, using character codes:

H4K (9/9/2010)
Or you can also Try this....
Replace(NAME,chr(34),"")
Regards,
Amar Sale

Or you can write a vb/c script component/task to use the .NET function.

In my case the .NET function performed the job regardless of the precise type of space character. Stands to reason it would work the same with double-quotes.

You could also push the work back to the SQL server level if it works correctly there.

karthik_j18 SSC Veteran Points: 294 More actions · Answer 7

You can also try double slashes instead of single slahes in the query.

Try like this,

REPLACE((NAME),"\\","")