SQL清理列中指定标签：移除<"blockquote至<"/blockquote>内容

Got it, let's fix this problem where you need to strip out all those <"blockquote...> chunks from your column, leaving only the text you want to keep. I'll cover solutions for the most common SQL databases since string handling can vary a lot between them.

First, Test Before You Update! #

Before running any UPDATE statements, always test the cleaning logic with a SELECT to make sure it works as expected. This avoids accidentally messing up your data.

MySQL/MariaDB (Version 8.0+) #

If you're on a newer version that supports REGEXP_REPLACE, this is the simplest approach. We'll use a non-greedy regex to match each blockquote tag and everything inside it, then replace it with an empty string.

-- Test first to verify results
SELECT 
  your_column,
  REGEXP_REPLACE(your_column, '<"blockquote[^>]*>.*?<"/blockquote>', '', 1, 0) AS cleaned_column
FROM your_table
WHERE your_column LIKE '%<"blockquote%';

-- If tests look good, run the update
UPDATE your_table
SET your_column = REGEXP_REPLACE(your_column, '<"blockquote[^>]*>.*?<"/blockquote>', '', 1, 0)
WHERE your_column LIKE '%<"blockquote%';

• Breakdown of the regex:
  • <"blockquote[^>]*>: Matches the opening tag, including any attributes (like class="twitter-tweet")
  • .*?: Non-greedy match for everything inside the blockquote (stops at the first closing tag instead of the last one)
  • <"/blockquote>: Matches the closing tag
  • The 1, 0 parameters mean "start at position 1" and "replace all occurrences"

PostgreSQL #

PostgreSQL's REGEXP_REPLACE works similarly, but uses a 'g' flag for global replacement instead of numeric parameters.

-- Test first
SELECT 
  your_column,
  REGEXP_REPLACE(your_column, '<"blockquote[^>]*>.*?<"/blockquote>', '', 'g') AS cleaned_column
FROM your_table
WHERE your_column LIKE '%<"blockquote%';

-- Update if tests pass
UPDATE your_table
SET your_column = REGEXP_REPLACE(your_column, '<"blockquote[^>]*>.*?<"/blockquote>', '', 'g')
WHERE your_column LIKE '%<"blockquote%';

SQL Server (2017+) #

SQL Server supports REGEXP_REPLACE starting in 2017, so you can use a similar regex approach:

-- Test first
SELECT 
  your_column,
  REGEXP_REPLACE(your_column, '<"blockquote[^>]*>.*?<"/blockquote>', '', 'g') AS cleaned_column
FROM your_table
WHERE your_column LIKE '%<"blockquote%';

-- Update statement
UPDATE your_table
SET your_column = REGEXP_REPLACE(your_column, '<"blockquote[^>]*>.*?<"/blockquote>', '', 'g')
WHERE your_column LIKE '%<"blockquote%';

For Older SQL Server Versions (Pre-2017) #

If you can't use REGEXP_REPLACE, a recursive CTE will work to repeatedly strip out blockquote tags until none are left:

-- Test with this CTE first
WITH RecursiveClean AS (
    SELECT 
        your_id, -- Use your primary key column here
        your_column,
        CHARINDEX('<"blockquote', your_column) AS start_pos,
        CHARINDEX('<"/blockquote>', your_column) AS end_pos
    FROM your_table
    WHERE your_column LIKE '%<"blockquote%'
    
    UNION ALL
    
    SELECT 
        your_id,
        STUFF(your_column, start_pos, end_pos - start_pos + LEN('<"/blockquote>'), '') AS your_column,
        CHARINDEX('<"blockquote', STUFF(your_column, start_pos, end_pos - start_pos + LEN('<"/blockquote>'), '')) AS start_pos,
        CHARINDEX('<"/blockquote>', STUFF(your_column, start_pos, end_pos - start_pos + LEN('<"/blockquote>'), '')) AS end_pos
    FROM RecursiveClean
    WHERE start_pos > 0
)
SELECT 
    t.your_id,
    t.your_column AS original_column,
    rc.your_column AS cleaned_column
FROM your_table t
JOIN RecursiveClean rc ON t.your_id = rc.your_id
WHERE rc.start_pos = 0;

-- If tests are correct, run the update
WITH RecursiveClean AS (
    SELECT 
        your_id,
        your_column,
        CHARINDEX('<"blockquote', your_column) AS start_pos,
        CHARINDEX('<"/blockquote>', your_column) AS end_pos
    FROM your_table
    WHERE your_column LIKE '%<"blockquote%'
    
    UNION ALL
    
    SELECT 
        your_id,
        STUFF(your_column, start_pos, end_pos - start_pos + LEN('<"/blockquote>'), '') AS your_column,
        CHARINDEX('<"blockquote', STUFF(your_column, start_pos, end_pos - start_pos + LEN('<"/blockquote>'), '')) AS start_pos,
        CHARINDEX('<"/blockquote>', STUFF(your_column, start_pos, end_pos - start_pos + LEN('<"/blockquote>'), '')) AS end_pos
    FROM RecursiveClean
    WHERE start_pos > 0
)
UPDATE t
SET t.your_column = rc.your_column
FROM your_table t
JOIN RecursiveClean rc ON t.your_id = rc.your_id
WHERE rc.start_pos = 0;

This recursive approach keeps stripping out the first occurrence of the blockquote tag until there are none left, ensuring all instances are removed.

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