apostrophe in db2 field

[bbbaaa]

apostrophe in db2 field


Hi,

I would like to know the syntax for translate function in DB2 for the following situation :

I have to do an insert from table1 to table2

insert into table1(last_name)

select translate(last_name,'''',''') from table2;

(last name has an apostrophe (O'brien))

but it doesn't work.

Please help ASAP. need to finish a project soon!

Thanks !

 

[stoggers]

Hi,

Apostrophes are dealt with by repeating them - ie '' would be interpreted as a single '. What do you want to achieve by using the translate function?

Stoggers.

 

[bbbaaa]

I realize double ' will do the job.
My issue is coding in sql.

I have to use the field name and not hard coded.

 

[stoggers]

Hi,

The 2nd string is invalid - '''. If you wan't to specify a single apostrophe this would have to be '''' - which doesn't make sense as this would then be the same as the 1st string.

This is why I asked what you were trying to achieve.

Regards,

Stoggers.

 

[stoggers]

Hi,

As an addendum...

The TRANSLATE function is a mapping function that enables the contents of the first string parameter to be translated according to the contents of the 2nd & 3rd string parameters.

eg TRANSLATE('ABCDE','1','A')

would result in '1BCDE'.

If you want to replace consecutive strings then use the REPLACE function.

Regards,

Stoggers.