Oracle SQL实现换行分隔值添加前后标签的查询方法

Absolutely, you can achieve this in Oracle SQL with a couple of reliable approaches—depending on how clean your input data is and which Oracle version you’re using. Let’s break down two practical methods:

Method 1: Simple Regex Replacement (for Clean Data) #

If your column values have no empty lines and each entry is properly separated by newlines (CHR(10)), you can use nested regex and string replacement to wrap each value and switch newlines to spaces in one go:

SELECT 
  TRIM(
    REPLACE(
      REGEXP_REPLACE(your_column, '([^\n]+)', '<Test>\1</Test>'),
      CHR(10), 
      ' '
    )
  ) AS formatted_values
FROM your_table;

How this works: #

1. REGEXP_REPLACE(your_column, '([^\n]+)', '<Test>\1</Test>'): Wraps every non-newline sequence (each individual value) in <Test> tags.
2. REPLACE(..., CHR(10), ' '): Replaces the original newline separators with spaces.
3. TRIM(): Removes any leading/trailing spaces that might result from leading/trailing newlines in the original data.

Method 2: Robust Split-and-Aggregate (Handles Empty Lines) #

For cases where your data might include empty lines or you need more control, split the string into individual rows, filter out empty entries, then concatenate back with wrapped tags. This uses XMLTABLE (available in Oracle 12c+):

SELECT 
  LISTAGG('<Test>' || x.value || '</Test>', ' ') 
    WITHIN GROUP (ORDER BY x.rn) AS formatted_values
FROM your_table t,
     XMLTABLE(
       'tokenize(., ''\n'')' 
       PASSING t.your_column 
       COLUMNS 
         value VARCHAR2(100) PATH '.',
         rn FOR ORDINALITY -- Preserves original line order
     ) x
WHERE TRIM(x.value) IS NOT NULL -- Skip empty/whitespace-only lines
GROUP BY t.your_primary_key; -- Replace with your table's unique identifier(s)

For Oracle Pre-12c: #

If you’re on an older version, use a CONNECT BY clause to split the string instead:

SELECT 
  LISTAGG('<Test>' || TRIM(SUBSTR(your_column, start_pos, end_pos - start_pos)) || '</Test>', ' ') 
    WITHIN GROUP (ORDER BY level) AS formatted_values
FROM (
  SELECT 
    your_column,
    your_primary_key,
    INSTR(your_column, CHR(10), 1, level - 1) + 1 AS start_pos,
    COALESCE(INSTR(your_column, CHR(10), 1, level), LENGTH(your_column) + 1) AS end_pos
  FROM your_table
  CONNECT BY level <= REGEXP_COUNT(your_column, CHR(10)) + 1
    AND PRIOR your_primary_key = your_primary_key
    AND PRIOR SYS_GUID() IS NOT NULL
)
WHERE TRIM(SUBSTR(your_column, start_pos, end_pos - start_pos)) IS NOT NULL
GROUP BY your_primary_key;

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