Manipulate returned data

UDA
Enthusiast

Manipulate returned data

Hello,
How do I manipulate returned data? For example, if return string is 'COOKIES & MILK', I need to replace '&' with its escape sequence "& amp ;" with no space in between :-) .

Thanks,
3 REPLIES
Enthusiast

Re: Manipulate returned data

Hi

Can you try something like the one shown below.

SELECT substr('CHOCO&MILK',0,POSITION('&' IN 'CHOCO&MILK')) ||
'(cHARACTER OF YOUR CHOICE)'||
SUBSTR('CHOCO&MILK',POSITION('&' IN 'CHOCO&MILK')+1,CHARACTERS('CHOCO&MILK')) "STRING";

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

STRING
----------------------------------------------
CHOCO(cHARACTER OF YOUR CHOICE)MILK

use any character in place of phrase (cHARACTER OF YOUR CHOICE). I hope this works
Enthusiast

Re: Manipulate returned data

Thanks Leo. It works like a charm. Although my issue is to replace all 5 special characters inside the same string (&,',",<,>). For example string is

(xml's stuff is > and < than 10" but not &)

Maybe I can try doing this recursively 5 times for 5 characters. I'll try it.
Enthusiast

Re: Manipulate returned data

Just for the heck of it, I did it recursively for string '&"<>'. It works but it is soooo ugly...