SQL查询中基于字段截取生成指定格式新列的实现方法咨询

Great question! The exact expression for your SELECT statement depends on which SQL dialect you're using, but here are tailored solutions for the most common databases that will produce your desired NewColumn values:

MySQL/MariaDB #

Use a combination of LOCATE, SUBSTRING, and a CASE statement to handle different URL formats, plus a WHERE clause to exclude entries like cnn.com/:

SELECT
    CASE
        -- For URLs with multiple path segments (e.g., yahoo.com/news/usa/today), take up to the end of the first segment
        WHEN LOCATE('/', ColumnName, LOCATE('/', ColumnName) + 1) > 0 THEN
            SUBSTRING(ColumnName, 1, LOCATE('/', ColumnName, LOCATE('/', ColumnName) + 1) - 1)
        -- For URLs with a single non-trailing-slash segment (e.g., espn.com/nfl), keep as-is
        WHEN RIGHT(ColumnName, 1) != '/' THEN
            ColumnName
        -- For URLs with a single trailing-slash segment (e.g., msn.com/en-us/), remove the trailing slash
        ELSE
            SUBSTRING(ColumnName, 1, LENGTH(ColumnName) - 1)
    END AS NewColumn
FROM your_table
WHERE ColumnName NOT REGEXP '^[^/]+/$';

PostgreSQL #

PostgreSQL uses STRPOS instead of LOCATE, but the logic is identical:

SELECT
    CASE
        WHEN STRPOS(ColumnName, '/', STRPOS(ColumnName, '/') + 1) > 0 THEN
            SUBSTRING(ColumnName FROM 1 FOR STRPOS(ColumnName, '/', STRPOS(ColumnName, '/') + 1) - 1)
        WHEN RIGHT(ColumnName, 1) != '/' THEN
            ColumnName
        ELSE
            SUBSTRING(ColumnName FROM 1 FOR LENGTH(ColumnName) - 1)
    END AS NewColumn
FROM your_table
WHERE ColumnName !~ '^[^/]+/$';

SQL Server #

For SQL Server, use CHARINDEX for finding slash positions:

SELECT
    CASE
        WHEN CHARINDEX('/', ColumnName, CHARINDEX('/', ColumnName) + 1) > 0 THEN
            SUBSTRING(ColumnName, 1, CHARINDEX('/', ColumnName, CHARINDEX('/', ColumnName) + 1) - 1)
        WHEN RIGHT(ColumnName, 1) != '/' THEN
            ColumnName
        ELSE
            SUBSTRING(ColumnName, 1, LEN(ColumnName) - 1)
    END AS NewColumn
FROM your_table
-- For SQL Server 2016+ (supports REGEXP_LIKE)
WHERE NOT REGEXP_LIKE(ColumnName, '^[^/]+/$');

-- For older SQL Server versions without regex support:
-- WHERE RIGHT(ColumnName, 1) != '/' OR CHARINDEX('/', ColumnName) != LEN(ColumnName);

How It Works #

• The CASE statement handles three scenarios: URLs with multiple path segments, single segments without trailing slashes, and single segments with trailing slashes.
• The WHERE clause filters out any rows where the URL is just a domain followed by a slash (like cnn.com/), which matches your desired output.

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