基于Scala Spark DataFrame的手机号验证及Correct字段更新需求

Got it, let's break down how to validate phone numbers and set the Correct column to "Y" (valid) or "N" (invalid)—here are practical solutions for the most common tools you might be using:

1. Excel (Spreadsheet Formula) #

For Excel 365/2021 (Regex Support) #

Use REGEXMATCH paired with IF to directly set the Correct value in your spreadsheet:

=IF(REGEXMATCH(A2,"^1[3-9]\d{9}$"),"Y","N")

• The regex ^1[3-9]\d{9}$ targets standard Chinese mobile numbers (11 digits starting with 1, followed by 3-9, then 9 digits). Tweak this pattern if you're working with numbers from other regions.
• Drag the formula down to apply it to all rows in your Correct column.

For Older Excel Versions (No Regex) #

If regex isn't available, use a combination of length and character checks instead:

=IF(AND(LEN(A2)=11,MID(A2,1,1)="1",ISNUMBER(--MID(A2,2,10))),"Y","N")

• This verifies the number is 11 digits long, starts with "1", and the remaining 10 characters are numeric values.

2. Python (Pandas for DataFrames) #

If you're working with structured data in Python, Pandas makes this task quick and scalable. Here's a complete example:

import pandas as pd
import re

# Load your data (replace with your file path or data source)
df = pd.read_csv("your_data.csv")

# Define a validation function (customize regex for your region)
def validate_phone(phone_num):
    # Handle empty/NaN values first
    if pd.isna(phone_num):
        return "N"
    # Regex for standard Chinese mobile numbers—adjust as needed
    phone_pattern = r"^1[3-9]\d{9}$"
    # Return "Y" if match, else "N"
    return "Y" if re.fullmatch(phone_pattern, str(phone_num)) else "N"

# Update the Correct column
df["Correct"] = df["Phone number"].apply(validate_phone)

# Save the updated data
df.to_csv("updated_data.csv", index=False)

• For international numbers, use an E.164 compliant regex like r"^\+[1-9]\d{1,14}$" instead.

3. SQL (Database Update) #

If your data lives in a database, use a CASE statement with regex matching to update the Correct column directly.

MySQL/MariaDB #

UPDATE your_table_name
SET Correct = CASE 
    WHEN `Phone number` REGEXP '^1[3-9][0-9]{9}$' THEN 'Y'
    ELSE 'N'
END;

PostgreSQL #

PostgreSQL uses the ~ operator for regex matches:

UPDATE your_table_name
SET Correct = CASE 
    WHEN "Phone number" ~ '^1[3-9][0-9]{9}$' THEN 'Y'
    ELSE 'N'
END;

• To allow optional formatting (like parentheses or hyphens), modify the regex—for example: '^1[3-9]\d{2}[-.]?\d{3}[-.]?\d{4}$' to match numbers like 138-1234-5678.

Note: Always test the regex pattern with a sample of your data to make sure it covers all valid cases for your region!

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