escape single quote in a column

Question

Post reply

escape single quote in a column

sqlfriend

SSC Guru

Points: 52781
More actions
March 26, 2014 at 3:28 pm

#148524

We have a SSIS package that pulls data from an oracle database and output to a flat file extract.
The end user complains that the middle name one person has has a single quote in between two words, so it caused the failure of import of the extract.
So we need to remove the single quote in the middle name for them in the extracts.
How can I achive that?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 1

My 2 cent;

An apostrophe is a valid character in a surname so that should be catered for.

Phil Parkin SSC Guru Points: 246993 More actions · Answer 2

Eirikur Eiriksson (3/26/2014)
My 2 cent;
An apostrophe is a valid character in a surname so that should be catered for.

I agree - if you remove these, you are changing people's names.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

Sorry, check the requirement with the user again, she modified the requirment, it is actually the user entered middle name with a double quote around it, the case is like this:

On the user interface, it is entered:

Last name: Helm

First name: Herminia

Middle name: "Minia"

Prefered name: Minia

In the extract, we only pulll firstname, lastname, and middle initial, so the middle initial turns out to be "

and cause the user importing from the extract flat file fail.

How to avoid this in code?

Thanks,

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 4

One suggestion is to use derived column and the replace function inside the left function.