Hey there! I get you're stuck with replacing special characters like vertical tabs (VT), line feeds, and carriage returns in DB2 since REGEXP_REPLACE isn't working for you. Let's go through some reliable, regex-free methods that work across most DB2 versions.

First, let's cover the two common scenarios you might need: #

Scenario 1: Remove the special characters from the string (replace them with empty string)

Use the TRANSLATE function—it's a lightweight, widely supported way to batch-replace multiple characters at once. Here's how:

SELECT
  TRANSLATE(your_column, '', CHR(10) || CHR(13) || CHR(11)) AS cleaned_column
FROM your_table;

• CHR(10) = Line Feed (LF)
• CHR(13) = Carriage Return (CR)
• CHR(11) = Vertical Tab (VT)
  The TRANSLATE function takes the input column, replaces every character in the third argument (our special chars) with the corresponding character in the second argument (empty string, so they get removed). If you need to handle more special characters, just add their CHR() values to the concatenated string—for example, CHR(9) for regular tabs.

Alternatively, if you prefer nested REPLACE (works even in very old DB2 versions):

SELECT
  REPLACE(REPLACE(REPLACE(your_column, CHR(10), ''), CHR(13), ''), CHR(11), '') AS cleaned_column
FROM your_table;

Scenario 2: Set the entire column to NULL if it contains any of these special characters

If your goal is to nullify the entire value whenever any of the special chars are present, use a CASE statement with LOCATE:

SELECT
  CASE
    WHEN LOCATE(CHR(10), your_column) > 0
      OR LOCATE(CHR(13), your_column) > 0
      OR LOCATE(CHR(11), your_column) > 0
    THEN NULL
    ELSE your_column
  END AS cleaned_column
FROM your_table;

LOCATE checks if the special character exists in the column—if any are found, we return NULL; otherwise, we keep the original value.

Quick note on REGEXP_REPLACE not working: #

If you're on a DB2 version older than 10.1, REGEXP_REPLACE isn't available. Even on newer versions, you might need to enable regex support via database configuration (like setting DB2_COMPATIBILITY_VECTOR to include regex capabilities). But the methods above are universal and don't depend on that.

内容的提问来源于stack exchange，提问作者Pranali