Wednesday, February 6, 2013

Single quotes in expressions

How to add single quotes in expressions? If you need the expresssion to log some execution parameters, for example? I sometimes do it so it would be easy to re-run a stored procedure manually after error.

However I could not find a proper escape character that would allow single quotes inside the expression. The workaround is to use:
|| CGR(39) ||
inside the expression. Not very convenient... Here are exapmles found at https://community.informatica.com/docs/DOC-1694


EXAMPLE A:

Source data as follows:
'don't call'
Problem: How do you use the IIF function to compare the "don't" string when it contains an apostrophe. The following expression will not work:
IIF (INPUT_STRING='don't call', RESULT1,RESULT2)

Solution: The following tests where the INPUT_STRING contains the word "don't":
IIF (INPUT_STRING='don' || CHR(39) || 't', RESULT1,RESULT2)

EXAMPLE B:

The input is:
Hello World
The output should be:
'Hello World'
Problem: How do you write a string with 'Hello World' using the single quotes as a literal in an expression?

Solution: The following returns the literal phrase 'Hello World':
CHR(39)||'Hello World'||CHR(39)

1 comment: