如何在SQL中移除表字段字符串末尾的斜杠（含后续空格）？

Hey there! Let's solve this problem of cleaning up trailing slashes (and any spaces that might follow them) in your string values. You were right to think of CHARINDEX or PATINDEX—we can use both (or even a combination with other string functions) to handle this scenario. Let's walk through examples so you can see how it works.

First, let's set up a test table with sample data to cover all the cases you mentioned:

-- Create test table
CREATE TABLE TestURLs (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    URL VARCHAR(255) NOT NULL
);

-- Insert test data covering various scenarios
INSERT INTO TestURLs (URL)
VALUES
    ('https://example.com/'),          -- Trailing slash only
    ('https://example.com/   '),       -- Trailing slash + multiple spaces
    ('https://example.com'),           -- No trailing slash
    ('https://example.com/path/  '),   -- Path with trailing slash + spaces
    ('https://example.com//'),         -- Multiple trailing slashes
    ('https://example.com/ ');         -- Trailing slash + single space

Method 1: Using RTRIM + LEFT + CASE #

This approach first removes any trailing spaces, then checks if the resulting string ends with a slash. If it does, we strip that final slash:

SELECT
    ID,
    URL AS OriginalURL,
    CASE
        WHEN RTRIM(URL) LIKE '%/' THEN LEFT(RTRIM(URL), LEN(RTRIM(URL)) - 1)
        ELSE URL
    END AS CleanedURL
FROM TestURLs;

Method 2: Using PATINDEX for Pattern Matching #

PATINDEX lets us match a specific pattern directly—here, we're looking for a slash followed by zero or more spaces at the end of the string. This avoids needing to trim first, as the pattern accounts for spaces:

SELECT
    ID,
    URL AS OriginalURL,
    CASE
        WHEN PATINDEX('%/[ ]*$', URL) > 0 THEN LEFT(URL, PATINDEX('%/[ ]*$', URL) - 1)
        ELSE URL
    END AS CleanedURL
FROM TestURLs;

Both methods will give you the same cleaned results. If you need to update the actual values in your table instead of just selecting them, you can adapt the logic into an UPDATE statement:

-- Update using Method 1 logic
UPDATE TestURLs
SET URL = CASE
        WHEN RTRIM(URL) LIKE '%/' THEN LEFT(RTRIM(URL), LEN(RTRIM(URL)) - 1)
        ELSE URL
    END;

-- Or update using Method 2 logic
UPDATE TestURLs
SET URL = CASE
        WHEN PATINDEX('%/[ ]*$', URL) > 0 THEN LEFT(URL, PATINDEX('%/[ ]*$', URL) - 1)
        ELSE URL
    END;

Just a quick note: If your strings might have leading spaces that you don't want, you could add LTRIM alongside RTRIM (i.e., LTRIM(RTRIM(URL))) but that's optional based on your needs.

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