Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
stevelord
Specialist

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

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!

1 Solution

Accepted Solutions
MarcoWedel

Hi,

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

QlikCommunity_Thread_144734_Pic1.JPG.jpg

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

View solution in original post

3 Replies
MarcoWedel

Hi,

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

QlikCommunity_Thread_144734_Pic1.JPG.jpg

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

maxgro
MVP

replace(

replace(

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

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

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

stevelord
Specialist
Author

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.