如何过滤数据库中以QWER开头且仅后跟数字的字符串？

Got it, let's solve this problem step by step. You need to filter database records where the string starts with QWER followed only by pure digits—so entries like QWER0000001 are valid, but anything with letters, underscores, hyphens, etc. after QWER should be excluded.

Core Idea #

We need to match strings that meet two strict criteria:

1. Start exactly with the constant prefix QWER
2. Contain only digits after that prefix (no extra characters of any kind)

SQL Implementation by Database #

Different databases have slightly different ways to handle regex pattern matching. Here's how to do it in the most common ones:

MySQL/MariaDB

Use the REGEXP operator with a pattern that anchors the start and end of the string:

SELECT your_column_name
FROM your_table_name
WHERE your_column_name REGEXP '^QWER[0-9]+$';

Breakdown of the pattern:

• ^: Marks the start of the string (ensures QWER is at the very beginning)
• QWER: Exact constant prefix
• [0-9]+: Matches one or more digits
• $: Marks the end of the string (ensures no extra characters come after the digits)

PostgreSQL

PostgreSQL uses the ~ operator for regex matching, with the same pattern:

SELECT your_column_name
FROM your_table_name
WHERE your_column_name ~ '^QWER[0-9]+$';

SQL Server

For SQL Server, you have two options depending on your version:
Option 1 (using LIKE for compatibility with older versions):

SELECT your_column_name
FROM your_table_name
WHERE your_column_name LIKE 'QWER[0-9][0-9]%' -- Ensures at least one digit after QWER
  AND your_column_name NOT LIKE 'QWER%[^0-9]%'; -- Rejects any string with non-digit characters after QWER

Option 2 (using REGEXP_LIKE for SQL Server 2016+):

SELECT your_column_name
FROM your_table_name
WHERE REGEXP_LIKE(your_column_name, '^QWER[0-9]+$');

Test Case Validation #

Let's check your sample inputs against this logic:

• ✅ QWER0000001: Passes (only digits follow QWER)
• ❌ QWERD00909: Fails (contains the letter D after QWER)
• ❌ QWER32_333: Fails (contains an underscore _)
• ❌ QWER32-333: Fails (contains a hyphen -)

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