Handling Single Quotation Marks

[PSULionRP]

WOW! This is a goodie.

We have embedded SQL within a FoxPro program. We have run into issues when
there is a hyphen in the last name: Most notably our Irish friends...O'Brien,
O'Donnell, etc..

Is there any easy way to handle this within native SQL or should I parse it
out prior to attempting the SQL???

I'm just trying to sit here and wonder if I need a whole different SQL
statement just to handle our O' people.

Any help or guidance would be GREATLY appreciated.

Thanks in advance for your help.

PSULionRP

[TheSQLGuru]

Can you tell us exactly WHAT you are doing with these names? Are you
creating a string for insert such as:

insert mytable values ('o'brien')

that will break. You can put in double single quotes to make that work:
insert mytable values ('o''brien')

or you could perhaps use set quoted_identifier off and use double quotes to
delimit the string (not recommended unless you know you have and will never
have column names that require escapes):

set quoted_identifier off --on the spid, just need this one time for as long
as connection is held
insert mytable values ("o'brien")
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

Post by PSULionRP
WOW! This is a goodie.
We have embedded SQL within a FoxPro program. We have run into issues when
there is a hyphen in the last name: Most notably our Irish
friends...O'Brien,
O'Donnell, etc..
Is there any easy way to handle this within native SQL or should I parse it
out prior to attempting the SQL???
I'm just trying to sit here and wonder if I need a whole different SQL
statement just to handle our O' people.
Any help or guidance would be GREATLY appreciated.
Thanks in advance for your help.
PSULionRP

[Erland Sommarskog]

Post by PSULionRP
WOW! This is a goodie.
We have embedded SQL within a FoxPro program. We have run into issues
when there is a hyphen in the last name: Most notably our Irish
friends...O'Brien, O'Donnell, etc..
Is there any easy way to handle this within native SQL or should I parse
it out prior to attempting the SQL???
I'm just trying to sit here and wonder if I need a whole different SQL
statement just to handle our O' people.

I don't know exactly how to do with Foxpro, but parameterised statements
is the way to go. You should never interpolate user input in query
strings. Least of all if you want to make friends with Brian O'Brien...

[Richard Mueller [MVP]]

Post by PSULionRP
WOW! This is a goodie.
We have embedded SQL within a FoxPro program. We have run into issues when
there is a hyphen in the last name: Most notably our Irish
friends...O'Brien,
O'Donnell, etc..
Is there any easy way to handle this within native SQL or should I parse it
out prior to attempting the SQL???
I'm just trying to sit here and wonder if I need a whole different SQL
statement just to handle our O' people.
Any help or guidance would be GREATLY appreciated.
Thanks in advance for your help.
PSULionRP

Replace all instances of "'" with "''".

[Michael Cole]

Post by PSULionRP
WOW! This is a goodie.
We have embedded SQL within a FoxPro program. We have run into issues
when there is a hyphen in the last name: Most notably our Irish
friends...O'Brien, O'Donnell, etc..

Oh, and BTW, its an apostrophe, not a hyphen. A hyphen is a dash -