求Teradata中OReplace函数的替代SQL实现方案

Got it, let's figure out how to work around your DBA's restriction on Teradata's OReplace function. Your example is SELECT OReplace('Hello World!','!','') which removes the exclamation mark from the string, so here are a few solid equivalent approaches you can use:

Method 1: Use TRANSLATE (Best for Single-Character Replacements) #

If you're only dealing with single characters (like your exclamation mark example), TRANSLATE is the simplest drop-in replacement. It maps specific characters to new values—here, we map ! to an empty string to remove it:

SELECT TRANSLATE('Hello World!' USING '!' AS '') AS replaced_string;

This will return exactly Hello World, just like your original OReplace call. Keep in mind this works best for single characters; for longer substrings, we need a different approach.

Method 2: Combine STRPOS + SUBSTRING (For Multi-Character Substrings) #

If you ever need to replace or remove longer substrings (not just single characters), you can pair string position and substring functions to replicate OReplace's behavior. Let's walk through an example:

Suppose you wanted to run OReplace('Hello World!', 'World', 'There') (which returns Hello There!). Here's the equivalent:

SELECT 
  CASE 
    WHEN STRPOS('Hello World!', 'World') > 0 
    THEN SUBSTRING('Hello World!' FROM 1 FOR STRPOS('Hello World!', 'World') - 1) 
         || 'There' 
         || SUBSTRING('Hello World!' FROM STRPOS('Hello World!', 'World') + LENGTH('World'))
    ELSE 'Hello World!'
  END AS replaced_string;

How this works: #

• STRPOS finds where the target substring (World) starts in the original string.
• The first SUBSTRING grabs everything before that starting position.
• We concatenate the replacement string (There).
• The second SUBSTRING grabs everything after the end of the target substring.
• The CASE statement handles cases where the substring doesn't exist, returning the original string instead of breaking.

To adapt this to your original example (removing !), just swap the replacement string with an empty string:

SELECT 
  CASE 
    WHEN STRPOS('Hello World!', '!') > 0 
    THEN SUBSTRING('Hello World!' FROM 1 FOR STRPOS('Hello World!', '!') - 1) 
         || SUBSTRING('Hello World!' FROM STRPOS('Hello World!', '!') + LENGTH('!'))
    ELSE 'Hello World!'
  END AS replaced_string;

Method 3: Use REGEXP_REPLACE (If Regex Is Allowed) #

If your DBA doesn't block regular expression functions, REGEXP_REPLACE is a flexible option that works for both single and multi-character scenarios. For your original example:

SELECT REGEXP_REPLACE('Hello World!', '!', '') AS replaced_string;

For multi-character replacements, it's even more straightforward:

SELECT REGEXP_REPLACE('Hello World!', 'World', 'There') AS replaced_string;

Just note that regex functions can have slightly different performance compared to OReplace, so test it with your data to make sure it's acceptable.

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