Oracle 11g中用REGEXP_REPLACE优化DN格式转AD风格输出

Hey there! I see you're working on converting LDAP-style Distinguished Names (DNs) to a cleaner AD-like format, and you want to ditch that clunky chain of regex functions for something more concise. Let's fix that with a streamlined REGEXP_REPLACE approach that handles the conversion in clean, readable steps.

For Single OU DNs (Your Original Scenario) #

If your DNs follow the fixed structure cn=name,ou=folder,dc=hostname,dc=com, you can use a single regex to match all components and rearrange them directly:

SELECT 
  REGEXP_REPLACE(
    TEST,
    '^CN=([^,]+),OU=([^,]+),DC=([^,]+),DC=([^,]+)$',
    '\1\\\2\\\3.\4',
    1,
    1,
    'i'
  ) AS ad_style_path
FROM your_table;

How This Works: #

• ^CN=([^,]+): Captures the object name (after CN=) by matching all characters up to the first comma.
• ,OU=([^,]+): Captures the folder name (after OU=) using the same logic.
• ,DC=([^,]+),DC=([^,]+)$: Captures the two domain components (hostname and suffix) and uses $ to ensure we match the full string end-to-end.
• The replacement pattern \1\\\2\\\3.\4 stitches everything together:
  • \1 = object name, followed by \\ to add a literal backslash
  • \2 = folder name, another \\ backslash
  • \3.\4 combines the two DC parts into hostname.com

For example, input cn=jane,ou=marketing,dc=corp,dc=net becomes jane\marketing\corp.net — exactly the format you're targeting.

For DNs with Multiple OUs (Extended Scenario) #

If you might have nested OUs like cn=bob,ou=team,ou=engineering,dc=corp,dc=com and want output like bob\team\engineering\corp.com, use a two-step regex to handle nested OUs cleanly:

SELECT 
  REGEXP_REPLACE(
    -- First, replace CN and all OUs with backslash-separated path
    REGEXP_REPLACE(TEST, '^(CN=([^,]+),)?(OU=([^,]+),)+', '\2\\\4\\', 1, 0, 'i'),
    -- Trim trailing backslash from the OU path
    '(.*)\\', '\1'
  ) || REGEXP_REPLACE(TEST, '.*DC=([^,]+),DC=([^,]+)$', '\1.\2', 1, 1, 'i') AS ad_style_path
FROM your_table;

This approach first captures all nested OUs and the CN, joins them with backslashes, trims any trailing backslash, then appends the formatted domain.

Why This Is Better Than Your Current Code #

Instead of chaining multiple REGEXP_REPLACE and SUBSTR calls, this method matches the entire DN structure in one (or two, for edge cases) logical steps. It's easier to read, maintain, and adjust if your DN structure changes down the line.

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