查询含指定特殊字符的列数据：SQL语句无结果排查

Got it, let's break down why your query isn't returning results and fix it step by step:

First off, the biggest issue with your current query is reversed logic! You want to extract rows where the Street column contains your specified special characters, but NOT LIKE '%[^A-Za-z0-9, ]%' does the exact opposite—it only returns rows with no characters outside of letters, numbers, commas, and spaces. That's why you get zero results even though the base query works.

Step 1: Fix the Core Query Logic #

Remove the NOT keyword to target rows with special characters:

SELECT a.Street
FROM ADRC a
WHERE a.Street LIKE '%[^A-Za-z0-9, ]%'

This will pull any Street value that includes at least one character not in the allowed set (letters, numbers, commas, spaces).

Step 2: Handle Non-ASCII Special Character Matching #

Since your target characters include Unicode ones like Ä, ß, and α, the basic LIKE operator might not reliably match them depending on your database (ADRC is a SAP table, so likely SAP HANA or SQL Server). Here's how to adjust:

For SAP HANA #

Use REGEXP_LIKE for precise, explicit matching of your specific special characters:

SELECT a.Street
FROM ADRC a
WHERE REGEXP_LIKE(a.Street, '[&*,.`~¿ÄÅÇÉÑÖÜßàáâäåçèéêëìíîïñòóôöùúûüÿƒα:;]')

This regex directly checks for any of the special characters you listed, avoiding inconsistencies with the negation set in LIKE.

For SQL Server #

If Street is an NVARCHAR (Unicode) column, add the N prefix to the pattern to ensure Unicode characters are recognized:

SELECT a.Street
FROM ADRC a
WHERE a.Street LIKE N'%[^A-Za-z0-9, ]%'

You can also use PATINDEX for explicit character matching:

SELECT a.Street
FROM ADRC a
WHERE PATINDEX(N'%[&*,.`~¿ÄÅÇÉÑÖÜßàáâäåçèéêëìíîïñòóôöùúûüÿƒα:;]%', a.Street) > 0

Step 3: Debug If Results Still Don't Show Up #

If you're still empty-handed:

• Test individual characters to confirm they exist in your data. For example:

  SELECT * FROM ADRC WHERE Street LIKE '%Ä%'
  

  This will tell you if that specific character is present, helping you narrow down which characters might not be matching.
• Check for hidden characters (like tabs or newlines) that might be the "special characters" in your data instead of the ones you listed.
• Verify the column's data type: if it's a non-Unicode VARCHAR type, some special characters might be stored incorrectly, leading to mismatches.

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