MySQL去除邮箱首尾制表符类空白字符，精准匹配查询订单号

Got it, let's fix this whitespace issue once and for all. Your current REPLACE() call only handles regular spaces, but those sneaky tabs, full-width spaces, and other invisible whitespace characters are slipping through. Here’s how to tackle this depending on your database system:

1. MySQL/MariaDB #

Use the TRIM() function with explicit whitespace characters to target tabs, line breaks, non-breaking spaces, and full-width spaces. This lets you strip all unwanted whitespace from the start and end of the email address without using wildcards.

SELECT Ordernumber 
FROM orders 
JOIN customers ON orders.CustomerNumber = customers.CustomerNumber 
WHERE TRIM(BOTH '\t\n\r\x00\xA0\u3000' FROM EmailAddress) = 'lewis@test.com';

• Breakdown of characters in TRIM:
  • \t: Tab character
  • \n: Newline
  • \r: Carriage return
  • \x00: Null character
  • \xA0: Non-breaking space (common in copy-pasted text)
  • \u3000: Chinese full-width space

2. PostgreSQL #

Leverage regex to strip all leading/trailing whitespace characters (PostgreSQL's \s covers Unicode whitespace, including full-width spaces and tabs).

SELECT Ordernumber 
FROM orders 
JOIN customers ON orders.CustomerNumber = customers.CustomerNumber 
WHERE REGEXP_REPLACE(EmailAddress, '^\s+|\s+$', '', 'g') = 'lewis@test.com';

• ^\s+: Matches one or more whitespace characters at the start of the string
• \s+$: Matches one or more whitespace characters at the end
• 'g': Ensures all matches (not just the first) are replaced

3. SQL Server (2017+) #

Use TRIM() with explicit character codes to cover tabs, line breaks, and full-width spaces:

SELECT Ordernumber 
FROM orders 
JOIN customers ON orders.CustomerNumber = customers.CustomerNumber 
WHERE TRIM(CHAR(9) + CHAR(10) + CHAR(13) + CHAR(160) + N'　' FROM EmailAddress) = 'lewis@test.com';

• CHAR(9): Tab
• CHAR(10): Newline
• CHAR(13): Carriage return
• CHAR(160): Non-breaking space
• N'　': Chinese full-width space (note the full-width space character inside the quotes)

Pro Tip: Identify Exact Whitespace Characters #

If you’re unsure which specific whitespace is causing issues, run this query to inspect the hex values of suspect email addresses:

SELECT EmailAddress, HEX(EmailAddress) 
FROM customers 
WHERE EmailAddress LIKE '%lewis@test.com%';

The hex output will tell you exactly which characters are padding the email, so you can add them to your TRIM or regex pattern for perfect precision.

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