MySQL中如何用‘SA 181’查询‘SA-181’记录（不使用‘-’）

Got it, let's break down how to make your query work so you can pull all records for "SA-181" using "SA 181" as your search term. Here are a few reliable approaches tailored to different SQL dialects and needs:

1. Replace Hyphens in the Column for Exact Matching #

This is the most straightforward way to align your search value with the stored data. We'll swap out the hyphen in the spec_no field with a space, then compare it directly to your query string.

SELECT * FROM material_1a WHERE REPLACE(spec_no, '-', ' ') = 'SA 181';

• Pros: Exact match, so you won't get unrelated records. Works across most SQL databases (MySQL, PostgreSQL, SQL Server, etc.).
• Heads up: If your table has a ton of rows, using REPLACE on the column will bypass any existing index on spec_no. For better performance, you can add a functional index (example for MySQL):

  CREATE INDEX idx_spec_no_space ON material_1a(REPLACE(spec_no, '-', ' '));
  

2. Use Regular Expressions for Flexible Matching #

If you need to account for both hyphens and spaces (or even other potential separators) in the stored data, regex is your friend. The syntax varies a bit by database:

MySQL/MariaDB #

SELECT * FROM material_1a WHERE spec_no REGEXP 'SA[- ]181';

PostgreSQL #

SELECT * FROM material_1a WHERE spec_no ~ 'SA[- ]181';

SQL Server #

SQL Server's regex support is a bit limited, so you can use PATINDEX or multiple LIKE conditions:

SELECT * FROM material_1a WHERE PATINDEX('%SA[- ]181%', spec_no) > 0;
-- Or simpler for exact format matches:
SELECT * FROM material_1a WHERE spec_no IN ('SA-181', 'SA 181');

• Pros: Handles multiple separator types in one query.
• Heads up: Keep your regex tight to avoid matching unintended values (e.g., SA_181 if you don't want those results).

3. Fuzzy Matching with LIKE (Use Cautiously) #

If you're confident that all relevant records follow the pattern of SA[separator]181, a LIKE query can work, but be careful—it might pull in unexpected matches.

SELECT * FROM material_1a WHERE spec_no LIKE 'SA%181';

• Pros: Super simple to write.
• Cons: Could match strings like SAX181 or SA123181 if they exist in your table. Only use this if you know the data format is strictly controlled.

Bonus Tip for Long-Term Fix #

If this type of format mismatch is a recurring issue, consider standardizing the spec_no column (e.g., always store values with hyphens, or always with spaces). Then you can use exact matches every time, which is faster and less error-prone.

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