Teradata中拆分含大括号包裹多字段的列至独立列

Got it, let's solve this problem cleanly. Since your DataColumn always contains exactly those four fixed fields wrapped in curly braces, we have two reliable approaches depending on your Teradata version.

Method 1: Using Regular Expressions (Clean & Modern) #

If your Teradata environment supports REGEXP_SUBSTR (typically versions 14.10 and newer), this is the most straightforward way. We'll use regex to target each field's pattern and extract the value inside the braces.

Here's the full query:

SELECT
  DataColumn,
  -- Extract Name value
  REGEXP_SUBSTR(DataColumn, '\{Name = ([^}]+)\}', 1, 1, 'i', 1) AS Name,
  -- Extract Age value
  REGEXP_SUBSTR(DataColumn, '\{Age = ([^}]+)\}', 1, 1, 'i', 1) AS Age,
  -- Extract Gender value
  REGEXP_SUBSTR(DataColumn, '\{Gender = ([^}]+)\}', 1, 1, 'i', 1) AS Gender,
  -- Extract Date value
  REGEXP_SUBSTR(DataColumn, '\{Date = ([^}]+)\}', 1, 1, 'i', 1) AS Date
FROM YourTable;

Breakdown of the regex pattern: #

• \{ and \}: Escape the curly braces (they're special characters in regex)
• ([^}]+): A capture group that matches any character except a closing brace — this grabs exactly the field value we want
• The final 1 parameter tells REGEXP_SUBSTR to return the content of the first capture group (not the entire matched string like {Name = X})
• The 'i' flag makes the match case-insensitive (optional, remove it if you need exact case matching)

Method 2: Using String Manipulation Functions (Compatible with Older Versions) #

If regex isn't available, we can use Teradata's core string functions like INSTR and SUBSTR to locate and extract each value manually.

Here's the query:

SELECT
  DataColumn,
  -- Extract Name
  SUBSTR(
    DataColumn,
    INSTR(DataColumn, '{Name = ') + 8, -- Start after "{Name = " (8 characters long)
    INSTR(DataColumn, '}', INSTR(DataColumn, '{Name = ')) - (INSTR(DataColumn, '{Name = ') + 8) -- Length until the closing brace
  ) AS Name,
  -- Extract Age
  SUBSTR(
    DataColumn,
    INSTR(DataColumn, '{Age = ') + 7, -- Start after "{Age = " (7 characters long)
    INSTR(DataColumn, '}', INSTR(DataColumn, '{Age = ')) - (INSTR(DataColumn, '{Age = ') + 7)
  ) AS Age,
  -- Extract Gender
  SUBSTR(
    DataColumn,
    INSTR(DataColumn, '{Gender = ') + 10, -- Start after "{Gender = " (10 characters long)
    INSTR(DataColumn, '}', INSTR(DataColumn, '{Gender = ')) - (INSTR(DataColumn, '{Gender = ') + 10)
  ) AS Gender,
  -- Extract Date
  SUBSTR(
    DataColumn,
    INSTR(DataColumn, '{Date = ') + 8, -- Start after "{Date = " (8 characters long)
    INSTR(DataColumn, '}', INSTR(DataColumn, '{Date = ')) - (INSTR(DataColumn, '{Date = ') + 8)
  ) AS Date
FROM YourTable;

How this works: #

1. INSTR(DataColumn, '{Name = ') finds the starting position of the {Name = string
2. We add the length of that string (8) to get the start of the actual value
3. The second INSTR finds the closing } that comes right after the {Name = section
4. Subtract the start position from the closing brace position to get the length of the value, then use SUBSTR to extract it

Both methods will reliably split your DataColumn into individual columns for each field. Just replace YourTable with your actual table name, and test with your sample data to confirm!

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