topic Re: how can we replace one ' with two ' (chr(39))? in QlikView

how can we replace one ' with two ' (chr(39))?

stevelord — Thu, 04 Dec 2014 21:42:41 GMT

Hi a colleague is dealing with apostrophes in some text strings and we're stumped about how to replace a single apostrophe with two apostrophes (not a double quotation mark, two apostrophes).

The character in question is chr(39), we're just stumped on how to have it replace only cases where an apostrophe appears by itself with two apostrophes. (Please don't ask why. ) Thanks!

Re: how can we replace one ' with two ' (chr(39))?

MarcoWedel — Thu, 04 Dec 2014 22:55:19 GMT

Hi,

one solution to only replace single apostrophes with double apostrophes while leaving double apostrophes untouched could be:

LOAD *,
    Replace(Replace(Text,chr(39),chr(39)&chr(39)),chr(39)&chr(39)&chr(39)&chr(39),chr(39)&chr(39)) as Text2
INLINE [
    Text
    ABCDEFG
    "A'BCDEFG"
    "ABC''DEFG"
    "AB''CDE'FG"
    "ABC''DEF'G"
];

hope this helps

regards

Marco

Re: how can we replace one ' with two ' (chr(39))?

maxgro — Thu, 04 Dec 2014 23:23:24 GMT

replace(

replace(Text, chr(39)&chr(39), '@@'),

chr(39), chr(39)&chr(39)),

'@@', chr(39)&chr(39))

Re: how can we replace one ' with two ' (chr(39))?

stevelord — Thu, 04 Dec 2014 23:37:13 GMT

I'm sure these are the most genius expressions ever in the history of all qlikview script. 185 IQs for sure! Gysbert should be in awe of this string. Both answers are correct, I could only stamp the first one correct and the second one helpful. Thanks, you all are great.