字符串提取与清洗技术求助：处理sil.catalog_no字段内容

Hey there! Let's work through cleaning up your sil.catalog_no values to remove those unwanted prefixes (HU-, EMC-, US-) and the -UW suffix. I’ll share a couple of reliable approaches that should work even if your SQL version has limited function support.

Approach 1: Nested REPLACE (Most Compatible) #

This method uses basic REPLACE functions, which are supported in nearly all SQL dialects. We’ll strip out the unwanted patterns one by one:

SELECT 
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(catalog_no, '-UW', ''),  -- First remove the trailing -UW
                'HU-', ''),                     -- Remove HU- prefix
            'EMC-', ''),                       -- Remove EMC- prefix
        'US-', '') AS cleaned_catalog_no       -- Remove US- prefix
FROM sil;

How it works with your examples: #

• Input: HU-98010587 → After replacing HU-, we get 98010587
• Input: US-HU-88136FYT-719-UW → First remove -UW to get US-HU-88136FYT-719, then strip US- and HU- to end up with 88136FYT-719

Approach 2: Regular Expression Replace (For Modern SQL Versions) #

If your database supports REGEXP_REPLACE (like MySQL 8+, PostgreSQL, SQL Server 2017+), you can clean the string in one step with a regex pattern that targets both the prefixes and suffix:

MySQL/PostgreSQL: #

SELECT REGEXP_REPLACE(catalog_no, '^(HU-|EMC-|US-)|-UW$', '') AS cleaned_catalog_no
FROM sil;

SQL Server: #

SELECT REGEXP_REPLACE(catalog_no, '^(HU-|EMC-|US-)|-UW$', '', 1, 0, 'IgnoreCase') AS cleaned_catalog_no
FROM sil;

Regex breakdown: #

• ^(HU-|EMC-|US-): Matches any of the three prefixes at the start of the string
• |-UW$: Matches the -UW suffix at the end of the string
• The empty string replacement removes all matched patterns in one go.

Both methods should give you the cleaned values you need. Start with the nested REPLACE if you’re unsure about regex support in your environment!

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