MySQL正则表达式筛选：仅保留数字或数字+字符串的contracts字段

云上老周

2026-5-15

Solution for Filtering MySQL Contracts Column

Got it, let's tackle this problem step by step. Your goal is to keep rows where the contracts column is either all digits, or a mix of digits and non-digits, while excluding rows with only non-digit characters. Here are two straightforward ways to do this with regular expressions in MySQL:

Method 1: Match Any Entry Containing At Least One Digit

This approach works because both pure numeric values and mixed digit-string values share one key trait—they have at least one digit. We can use the REGEXP operator to target this:

SELECT * FROM your_table_name
WHERE contracts REGEXP '[0-9]';

How it works:

The regex [0-9] matches any string that contains at least one numeric character (0 through 9).
This automatically includes:
- Pure numeric values (e.g., '12345', '987')
- Mixed values (e.g., 'abc123', '456xyz', '78-test90')
It excludes pure non-numeric strings (e.g., 'hello', 'contract', '---') since they have no digits.

Method 2: Exclude Pure Non-Digit Strings Directly

If you prefer to explicitly exclude the pure string entries, you can use NOT REGEXP with a regex that targets strings made entirely of non-digits:

SELECT * FROM your_table_name
WHERE contracts NOT REGEXP '^[^0-9]+$';

How it works:

Let's break down the regex ^[^0-9]+$:
- ^ = start of the string
- [^0-9]+ = one or more characters that are NOT digits (the ^ inside the brackets negates the set)
- $ = end of the string
This regex matches only strings that are 100% non-numeric (your "pure string" category). Adding NOT in front excludes these rows, leaving exactly the entries you want.