SQL Server：从含职级的职位列提取去重职位名称

Got it, let's work through this. Since you can't modify the view and need to group all ranked Software Engineers (and other roles) under a single normalized title, the key is to reliably strip off those trailing rank identifiers. Here's how to approach this across common databases:

Core Approach #

We need to target the trailing rank patterns you mentioned: 1-3 character suffixes like numbers (2), single letters (C), or hyphenated codes (E-1). The goal is to remove these (plus the preceding space) from the job title, then pull distinct values.

Solution for SQL Server #

SELECT DISTINCT
  CASE
    -- Handle hyphenated ranks (e.g., E-1)
    WHEN RIGHT(jobtitle, 3) LIKE '[A-Z]-[0-9]' THEN TRIM(LEFT(jobtitle, LEN(jobtitle) - 4))
    -- Handle 2-character ranks (e.g., 10, A2)
    WHEN RIGHT(jobtitle, 2) LIKE '[0-9]{1,2}' OR RIGHT(jobtitle, 2) LIKE '[A-Z][0-9]' THEN TRIM(LEFT(jobtitle, LEN(jobtitle) - 2))
    -- Handle single-character ranks (e.g., 2, C)
    WHEN RIGHT(jobtitle, 1) LIKE '[A-Z0-9]' THEN TRIM(LEFT(jobtitle, LEN(jobtitle) - 2))
    -- Fallback for titles with no rank
    ELSE jobtitle
  END AS normalized_job_title
FROM your_view_name;

Solution for MySQL/MariaDB #

Regex replacement simplifies this (works for MySQL 8.0+):

SELECT DISTINCT
  REGEXP_REPLACE(
    jobtitle,
    -- Match space + rank patterns at the end of the string
    '[[:space:]]([A-Z]-[0-9]|[0-9]{1,2}|[A-Z]{1})$',
    ''
  ) AS normalized_job_title
FROM your_view_name;

Solution for PostgreSQL #

PostgreSQL’s regex support makes this straightforward too:

SELECT DISTINCT
  REGEXP_REPLACE(
    jobtitle,
    -- Target your specific rank patterns at the end of the string
    ' ([A-Z]-[0-9]|[0-9]{1,2}|[A-Z]{1})$',
    ''
  ) AS normalized_job_title
FROM your_view_name;

Testing Edge Cases #

Make sure to validate these scenarios to ensure the logic works:

• Software Engineer 6 → Software Engineer
• Designer B → Designer
• Air Traffic Controller E-3 → Air Traffic Controller
• Senior Director → stays Senior Director (no rank to strip)

If you hit edge cases where the regex catches valid title suffixes (e.g., a job title like Data Analyst 3 where "3" is part of the title, not a rank), you can tweak the regex to match your organization's exact rank format more closely.

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