如何在SQL SELECT语句中处理含‘Kod nr 1:’的字符串末尾内容

Got it, let's work through this SQL string handling requirement. You need to conditionally modify a column in your SELECT statement based on whether it contains the <strong>Kod nr 1:</strong> pattern—here's how to implement both versions of the desired output:

Option 1: Remove the "Kod nr 1:" section entirely #

This will strip off the <strong>Kod nr 1:</strong> XXXXX part, leaving you with the prefix string ending in a semicolon and space.

For MySQL/MariaDB: #

SELECT 
    CASE
        WHEN your_column LIKE '%<strong>Kod nr 1:</strong>%'
        THEN TRIM(TRAILING ' ' FROM SUBSTRING_INDEX(your_column, '<strong>Kod nr 1:</strong>', 1)) || ' '
        ELSE your_column
    END AS processed_column
FROM your_table;

• SUBSTRING_INDEX grabs everything before the first occurrence of the target pattern
• TRIM cleans up any extra trailing spaces before we add back a single space to match your desired format
• The CASE statement ensures we only modify rows that actually contain the pattern

For PostgreSQL: #

PostgreSQL uses slightly different string functions, so adjust it like this:

SELECT 
    CASE
        WHEN your_column LIKE '%<strong>Kod nr 1:</strong>%'
        THEN TRIM(TRAILING ' ' FROM LEFT(your_column, POSITION('<strong>Kod nr 1:</strong>' IN your_column) - 1)) || ' '
        ELSE your_column
    END AS processed_column
FROM your_table;

Option 2: Mask the numeric value with asterisks #

This replaces the numeric part after Kod nr 1: with ******, and also removes the <strong> tags to match your desired output format.

For MySQL/MariaDB: #

SELECT 
    CASE
        WHEN your_column LIKE '%<strong>Kod nr 1:</strong>%'
        THEN REPLACE(
            REPLACE(your_column, '<strong>', ''),
            SUBSTRING(your_column, LOCATE('<strong>Kod nr 1:</strong>', your_column) + LENGTH('<strong>Kod nr 1:</strong>')),
            '******'
        )
        ELSE your_column
    END AS processed_column
FROM your_table;

• First REPLACE removes the opening <strong> tag
• SUBSTRING captures everything after the Kod nr 1: pattern (including the number)
• The second REPLACE swaps that captured numeric part with ******
• Again, the CASE statement skips modification for rows without the pattern

For PostgreSQL: #

SELECT 
    CASE
        WHEN your_column LIKE '%<strong>Kod nr 1:</strong>%'
        THEN REPLACE(
            REPLACE(your_column, '<strong>', ''),
            SUBSTRING(your_column FROM POSITION('<strong>Kod nr 1:</strong>' IN your_column) + LENGTH('<strong>Kod nr 1:</strong>')),
            '******'
        )
        ELSE your_column
    END AS processed_column
FROM your_table;

Quick Simplification Tip: #

If the numeric value after Kod nr 1: is always exactly 6 digits, you can simplify the mask version by directly replacing the full pattern:

-- Example for MySQL
REPLACE(your_column, '<strong>Kod nr 1:</strong> 999999', 'Kod nr 1: ******')

But the earlier dynamic approach works even if the number length varies.

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