Firebird SQL “REPLACE” tips
Managing the “&” character with Firebird’s string manipulation functions sometimes may not yield the expected results.
The documentation for Firebird’s official “REPLACE” function: https://firebirdsql.org/refdocs/langrefupd25-intfunc-replace.html
For example, if we were to make the classic replacement “>” with “>” with an update, it would be normal to write:
|
1 2 |
UPDATE "YOUR_TB_NAME" SET "YOUR_TB_NAME"."TEXT_FIELD" = REPLACE("YOUR_TB_NAME"."TEXT_FIELD",'>','>') |
If this syntax does not produce the expected results, you can circumvent it by using the character code corresponding to “&” which in the ASCII table corresponds to the decimal code: 38
|
1 2 |
UPDATE "YOUR_TB_NAME" SET "YOUR_TB_NAME"."TEXT_FIELD" = REPLACE("YOUR_TB_NAME"."TEXT_FIELD",ASCII_CHAR (38) ||'gt;','>') |
In many SQL syntaxes, the “&” character used within the replace function is utilized as a symbol to make the “Replace” function behave slightly differently than expected.
On the other hand, if we wanted to replace “<BR>” with the corresponding “\n\r” or for Delphi lovers #10#13, we could perform the following string:
|
1 2 |
UPDATE "YOUR_TB_NAME" SET "YOUR_TB_NAME"."TEXT_FIELD" = REPLACE("YOUR_TB_NAME"."TEXT_FIELD",'<br>',ASCII_CHAR (10) || ASCII_CHAR (13)) |
