SQL整列字符串处理：移除/替换数字及提取数字生成新列

Got it, let's tackle your two column-wise string manipulation tasks step by step. I'll cover solutions using Python Pandas (the go-to tool for tabular data) and SQL in case you're working with a database, since both are common scenarios. We'll use your sample empid values: A12SD9, A12SA1, A10AA1.

用 Python Pandas #

First, let's set up a sample DataFrame to test with:

import pandas as pd

# Create sample data
data = {'empid': ['A12SD9', 'A12SA1', 'A10AA1']}
df = pd.DataFrame(data)

移除所有数字

Use str.replace() with a regex pattern to match digits (\d+) and replace them with an empty string:

# Add a new column with numbers removed
df['empid_no_numbers'] = df['empid'].str.replace(r'\d+', '', regex=True)

Result for empid_no_numbers: ['ASD', 'ASA', 'AAA']

替换数字为指定字符

If you want to replace digits with something else (like X), adjust the replacement string:

# Replace all digits with 'X'
df['empid_replaced_numbers'] = df['empid'].str.replace(r'\d', 'X', regex=True)

Result for empid_replaced_numbers: ['AXSXD', 'AXSAX', 'AXAAX']

用 SQL (MySQL example) #

移除所有数字

Use REGEXP_REPLACE to strip out digits:

SELECT 
  empid,
  REGEXP_REPLACE(empid, '[0-9]+', '') AS empid_no_numbers
FROM your_table;

替换数字为指定字符

Swap digits with your desired character:

SELECT 
  empid,
  REGEXP_REPLACE(empid, '[0-9]', 'X') AS empid_replaced_numbers
FROM your_table;

用 Python Pandas #

提取所有数字并合并为一个字符串

If you want all digits from each string combined into one value (e.g., A12SD9 → 129), use str.findall() to grab all digits, then str.join() to merge them:

# Extract all digits and join into a single string
df['empid_extracted_numbers'] = df['empid'].str.findall(r'\d').str.join('')

Result for empid_extracted_numbers: ['129', '121', '101']

提取第一个连续数字段

If you only need the first sequence of digits (e.g., A12SD9 → 12), use str.extract():

# Extract the first continuous group of digits
df['empid_first_num_segment'] = df['empid'].str.extract(r'(\d+)', expand=False)

Result for empid_first_num_segment: ['12', '12', '10']

用 SQL (MySQL example) #

To extract all digits from each string, use REGEXP_REPLACE to remove everything that's not a digit:

SELECT 
  empid,
  REGEXP_REPLACE(empid, '[^0-9]', '') AS empid_extracted_numbers
FROM your_table;

This will give you the same combined digit strings as the Pandas findall+join method.

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