Replace Apostrophes

[IanWaterman]

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

 

[r937]

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

r937.com | rudy.ca

 

[IanWaterman]

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

 

[JarlH]

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.