如何移除数据库表URL列特殊字符或进行可识别解码？

Absolutely! You’ve got two solid approaches here—either properly decode those special characters so they’re recognized correctly (the recommended option if possible), or strip them out entirely if that’s what your use case requires. Let’s break both down with examples for common databases:

First, it’s important to note that characters like ä, è, æ, µ are valid UTF-8 characters—often they appear "unrecognized" because of encoding mismatches during storage or retrieval. Fixing the encoding will make these URLs work as intended instead of removing valid characters.

MySQL #

• If the issue is an encoding mismatch, convert the column to use utf8mb4 (full UTF-8 support in MySQL):

  SELECT CONVERT(url_column USING utf8mb4) FROM your_table;
  

• If the URLs are URL-encoded (e.g., %C3%A4 for ä), use the built-in URL_DECODE() function (MySQL 8.0+):

  SELECT URL_DECODE(url_column) FROM your_table;
  

PostgreSQL #

• For encoding mismatches, convert the column to the correct charset:

  SELECT convert(url_column, 'LATIN1', 'UTF8') FROM your_table;
  

• For URL-encoded strings, use pg_decode_uri_component() (PostgreSQL 11+):

  SELECT pg_decode_uri_component(url_column) FROM your_table;
  

SQL Server #

• Fix encoding mismatches with collation conversion:

  SELECT CAST(url_column AS VARCHAR(MAX)) COLLATE SQL_Latin1_General_CP1_CI_AS FROM your_table;
  

• SQL Server doesn’t have a built-in URL decode function, but you can create a custom one:

  CREATE FUNCTION dbo.UrlDecode(@url NVARCHAR(MAX))
  RETURNS NVARCHAR(MAX)
  AS
  BEGIN
      DECLARE @decoded NVARCHAR(MAX) = ''
      DECLARE @i INT = 1
      WHILE @i <= LEN(@url)
      BEGIN
          IF SUBSTRING(@url, @i, 1) = '%' AND @i + 2 <= LEN(@url)
          BEGIN
              SET @decoded = @decoded + NCHAR(CONVERT(INT, '0x' + SUBSTRING(@url, @i + 1, 2), 16))
              SET @i = @i + 3
          END
          ELSE
          BEGIN
              SET @decoded = @decoded + SUBSTRING(@url, @i, 1)
              SET @i = @i + 1
          END
      END
      RETURN @decoded
  END
  

  Then use it like this:

  SELECT dbo.UrlDecode(url_column) FROM your_table;
  

If you truly need to eliminate these characters, use regex replacement or custom functions to target them specifically:

MySQL (8.0+) #

Use REGEXP_REPLACE() to keep only valid URL characters and remove everything else:

SELECT REGEXP_REPLACE(url_column, '[^a-zA-Z0-9:/?#&=._-]', '') FROM your_table;

PostgreSQL #

Use REGEXP_REPLACE() with the global flag to strip all non-valid URL characters:

SELECT REGEXP_REPLACE(url_column, '[^a-zA-Z0-9:/?#&=._-]', '', 'g') FROM your_table;

SQL Server (2017+) #

Use REGEXP_REPLACE() for regex-based removal:

SELECT REGEXP_REPLACE(url_column, N'[^a-zA-Z0-9:/?#&=._-]', '') FROM your_table;

Or create a function to target specific characters if you only want to remove ä, è, æ, µ:

CREATE FUNCTION dbo.RemoveSpecialChars(@input NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @specialChars NVARCHAR(100) = N'äèæµ' -- Add more characters if needed
    DECLARE @i INT = 1
    WHILE @i <= LEN(@specialChars)
    BEGIN
        SET @input = REPLACE(@input, SUBSTRING(@specialChars, @i, 1), '')
        SET @i = @i + 1
    END
    RETURN @input
END

Then call it:

SELECT dbo.RemoveSpecialChars(url_column) FROM your_table;

Quick Note #

Always back up your data before running any update queries on your table! Test these functions on a small subset of your data first to ensure they behave as expected.

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