.NET项目数据库数据验证：如何编写精准匹配85及85衍生格式数据的SQL查询

Got it, let's fix this query so it only pulls records where "85" is a distinct part of the SYNO value, not just a buried substring. Your original LIKE clause is too broad—it's catching things like 852 or 385 where 85 is just part of a longer number, which isn't what you want.

Solution for SQL Server #

We can use PATINDEX (pattern matching index) to target specific patterns where "85" appears as a standalone segment. Here's the adjusted query:

SELECT * 
FROM FullData 
WHERE 
    -- Matches 85 with non-digit characters on both sides (e.g., " 85 ", "x85/1")
    PATINDEX('%[^0-9]85[^0-9]%', CAST(SYNO AS VARCHAR(50))) > 0
    -- Matches 85 at the start followed by a non-digit (e.g., "85/A", "85-")
    OR PATINDEX('85[^0-9]%', CAST(SYNO AS VARCHAR(50))) > 0
    -- Matches 85 at the end preceded by a non-digit (e.g., "-85", " 85")
    OR PATINDEX('%[^0-9]85', CAST(SYNO AS VARCHAR(50))) > 0
    -- Matches the exact value "85"
    OR CAST(SYNO AS VARCHAR(50)) = '85'

Why this works: #

• We cast SYNO to a string first because your example includes both strings and numbers (like 13857)—this ensures consistent pattern matching across data types.
• The [^0-9] pattern matches any character that's NOT a digit, so we're ensuring "85" isn't stuck inside another number.
• We cover all edge cases: exact 85, 85 at the start/end with separators, and 85 in the middle with non-digit wrappers.

For Other Databases #

If you're using MySQL or PostgreSQL, you can use regular expressions directly:

MySQL:

SELECT * 
FROM FullData 
WHERE 
    CAST(SYNO AS CHAR) REGEXP '(^85[^0-9]|[^0-9]85[^0-9]|[^0-9]85$|^85$)'

PostgreSQL:

SELECT * 
FROM FullData 
WHERE 
    SYNO::TEXT ~ '(^85[^0-9]|[^0-9]85[^0-9]|[^0-9]85$|^85$)'

Quick Note on Performance #

Pattern matching with PATINDEX or regex can be slower on large tables since it can't use traditional indexes. If you're dealing with a huge dataset, consider:

• Adding a computed column that pre-processes SYNO to flag valid "85" entries, then index that column.
• Using full-text search if your database supports it (though that's overkill for this specific case).

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