提取同时含字母和数字的值遇问题：电话列查询未达预期求协助

Hey there! Let's work through this problem of filtering valid values (those with both letters and numbers) from your phone column, since your current query isn't giving you the expected results. Below are tailored solutions for common SQL dialects, plus some troubleshooting tips to check your existing code.

核心逻辑 #

We need to target records where the phone value has at least one number (0-9) AND at least one letter (a-z/A-Z). This automatically excludes pure numbers, pure letters, or other garbage values like symbols-only strings.

1. MySQL/MariaDB 实现 #

Use the REGEXP operator to check for both character types. You can either split the checks into two conditions or use a more concise regex with positive lookaheads (supported in MySQL 8.0+):

基础版（兼容所有MySQL版本）

SELECT phone_column
FROM your_table
WHERE phone_column REGEXP '[0-9]' 
  AND phone_column REGEXP '[a-zA-Z]';

简洁版（MySQL 8.0+）

SELECT phone_column
FROM your_table
WHERE phone_column REGEXP '(?=.*[0-9])(?=.*[a-zA-Z])';

2. PostgreSQL 实现 #

PostgreSQL uses the ~ operator for regex matching. Similar logic applies:

基础版

SELECT phone_column
FROM your_table
WHERE phone_column ~ '[0-9]'
  AND phone_column ~ '[a-zA-Z]';

严谨版（确保整个字符串符合要求）

SELECT phone_column
FROM your_table
WHERE phone_column ~ '^(?=.*[0-9])(?=.*[a-zA-Z]).+$';

3. SQL Server 实现 #

SQL Server doesn't support full regex natively, but you can use PATINDEX to check for the presence of numbers and letters:

SELECT phone_column
FROM your_table
WHERE PATINDEX('%[0-9]%', phone_column) > 0
  AND PATINDEX('%[a-zA-Z]%', phone_column) > 0;

排查现有查询的常见问题 #

If your original query failed, here are a few things to check:

• Case sensitivity: Did your regex only account for lowercase letters (e.g., [a-z] instead of [a-zA-Z])? Some databases are case-sensitive by default.
• Extra characters: Are there spaces, hyphens, or symbols in the phone column? You might need to clean the data first, like using TRIM() to remove whitespace:

  -- Example for MySQL: Clean whitespace before checking
  SELECT TRIM(phone_column) AS cleaned_phone
  FROM your_table
  WHERE TRIM(phone_column) REGEXP '[0-9]' 
    AND TRIM(phone_column) REGEXP '[a-zA-Z]';
  

• Overly strict regex: Did you write a regex that requires letters and numbers to be adjacent (e.g., [0-9][a-zA-Z])? That would miss values where letters and numbers are scattered (like AB123CD).

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