Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replace Apostrophes

Status
Not open for further replies.

IanWaterman

Programmer
Joined
Jun 26, 2002
Messages
3,511
Location
GB
I need to test and remove commas and apostrophes in a string.

The replace function works fine for commas, but is proving impossible for apostrophes.

Is there any way other than telling users to stop entering them into a name field. eg Company name is

Carol's Beauty Parlour

Strictly speaking if we sent them a customised letter they may be annoyed if we use Carols Beauty Parlour. However, another application into which we import this data does not like apostrophes and commas.

Any help suggestions gratefully received.

Ian
 
Code:
insert 
  into companies
     ( companyname , ... )
values
     ( 'Carol''s Beauty Parlour' , ...)

r937.com | rudy.ca
 
Found out how to do this. Not sure of the logic but it seems to work.

REPLACE(CLI_COMP_NAME, '''', '')

Somehow the first double apostrophe '' acts like a single apostrophe and allows the search character to be an apostrophe. I then replace with nothing.

Ian
 
Somehow the first double apostrophe '' acts like a single apostrophe and allows the search character to be an apostrophe. I then replace with nothing.

Actually the first ' means start of character string literal, the following double '' is used to specify one apostrophe within the string, and the final ' terminates the string.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top