编写兼容国内外邮政编码的T-SQL存储过程的技术咨询

Got it, let's work through this problem to build a stored procedure that handles every postal code format in your table—from NULL values to US 5/10-digit codes, alphanumeric international formats, and even entries with special characters like slashes.

Key Challenges to Address #

Your original query only handled 5-digit codes, and expanding to 10-digit broke 5-digit matches. The core issue is that raw postal code values have inconsistent formatting (case differences, special characters, varying lengths), so we need to standardize both the input parameter and the table data before comparing.

Step-by-Step Solution #

Here’s a stored procedure that normalizes data first, then matches across all scenarios:

CREATE PROCEDURE GetAddressesByPostalCode
    @postal_code VARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON;

    -- Normalize the input parameter: strip non-alphanumeric chars, convert to uppercase
    DECLARE @cleaned_input VARCHAR(10) = NULL;
    IF @postal_code IS NOT NULL
    BEGIN
        -- For SQL Server 2017+: Use TRANSLATE for clean, fast removal of special chars
        SET @cleaned_input = UPPER(
            TRANSLATE(@postal_code, '!@#$%^&*()-_+=[]{}|;:''",.<>?/`~ ', REPLICATE('', 32))
        );

        -- For older SQL Server versions (pre-2017), use this loop instead:
        /*
        SET @cleaned_input = UPPER(@postal_code);
        WHILE PATINDEX('%[^A-Z0-9]%', @cleaned_input) > 0
        BEGIN
            SET @cleaned_input = STUFF(@cleaned_input, PATINDEX('%[^A-Z0-9]%', @cleaned_input), 1, '');
        END
        */
    END

    -- Match logic: handle NULLs and normalized values
    SELECT *
    FROM table_name
    WHERE 
        -- Case 1: Input is NULL → only return rows with NULL postal_code
        (@postal_code IS NULL AND postal_code IS NULL)
        OR
        -- Case 2: Input is valid → match normalized table value to normalized input
        (
            @postal_code IS NOT NULL
            AND 
            UPPER(
                CASE 
                    WHEN postal_code IS NOT NULL THEN
                        TRANSLATE(postal_code, '!@#$%^&*()-_+=[]{}|;:''",.<>?/`~ ', REPLICATE('', 32))
                    ELSE ''
                END
            ) = @cleaned_input
        );
END
GO

How This Works #

1. Normalization:
   • Converts all characters to uppercase (so Wd3 and WD3 are treated the same)
   • Removes every non-alphanumeric character (slashes, spaces, hyphens, etc.—so ZH/8600 becomes ZH8600)
2. NULL Handling:
   • Explicitly matches NULL input to NULL postal_code values, covering addresses without postal codes
3. Universal Matching:
   • Works for every format in your sample data: 5-digit (46383), 10-digit (074523632), alphanumeric (B4H34, A1G), mixed-case (Wd3, 3he), and special-char entries (ZH/8600)

Optional: Prefix Matching (If Needed) #

If you want to match partial inputs (e.g., entering 46383 should return both 46383 and 463831234), replace the equality check with a LIKE clause:

UPPER(...) LIKE @cleaned_input + '%'

Just note this will return more results, so adjust based on your business needs.

Test Scenarios to Validate #

• NULL Input: EXEC GetAddressesByPostalCode @postal_code = NULL → Returns rows where postal_code is NULL
• 5-Digit US: EXEC GetAddressesByPostalCode @postal_code = '46383' → Matches 46383, 463 83, or 46383-1234 (after normalization)
• International Alphanumeric: EXEC GetAddressesByPostalCode @postal_code = 'Wd3' → Matches Wd3, WD3, or wd3
• Special Characters: EXEC GetAddressesByPostalCode @postal_code = 'ZH/8600' → Matches ZH/8600 or ZH8600

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