如何从test表中筛选类似customer 1、customer 2的名称，排除customer？

Hey there! Let's work through this SQL filtering problem together. You need to pull records from the test table where the nm field matches the format customer [number] (like "customer 1" or "customer 2") and exclude the plain "customer" entry. Here are a couple of reliable approaches:

Approach 1: Using LIKE with Wildcards #

This is a straightforward, database-agnostic method. The pattern 'customer %' targets entries that start with "customer " followed by any characters (in your case, numbers). Since the plain "customer" doesn't have that trailing space, it gets automatically excluded:

SELECT id, nm
FROM test
WHERE nm LIKE 'customer %';

If you want to make the exclusion explicit (for clarity or edge cases), you can add an extra condition to leave no room for ambiguity:

SELECT id, nm
FROM test
WHERE nm LIKE 'customer %'
  AND nm != 'customer';

Approach 2: Using Regular Expressions (More Precise) #

If your database supports regular expressions (most modern ones do), this method ensures you only match entries with customer followed by a space and one or more numbers—no accidental matches for entries like "customer abc":

MySQL/MariaDB: #

SELECT id, nm
FROM test
WHERE nm REGEXP '^customer [0-9]+$';

PostgreSQL: #

SELECT id, nm
FROM test
WHERE nm ~ '^customer [0-9]+$';

SQL Server: #

SELECT id, nm
FROM test
WHERE nm LIKE 'customer [0-9]%'
  AND nm NOT LIKE 'customer[^ ]%'; -- Ensures there's a space after "customer"

The regex breakdown for the strict match:

• ^ = Matches the start of the string
• customer = Exact literal match for the prefix (including the space)
• [0-9]+ = Matches one or more digits
• $ = Matches the end of the string

This guarantees you only get entries that strictly follow the "customer [number]" format.

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