PowerQuery如何提取列中第一个字母及其之前的所有内容？

Python 实现 #

利用正则表达式的非贪婪匹配，精准定位第一个字母并提取其及之前的所有内容：

import re

sample_data = ["12345A123", "23456B000", "45678D93"]
extracted_results = [re.search(r".*?[A-Za-z]", item).group() for item in sample_data]
print(extracted_results)  # 输出: ['12345A', '23456B', '45678D']

说明：.*? 会非贪婪匹配任意字符，直到遇到第一个大小写字母 [A-Za-z]，group() 取出匹配到的完整内容。

Excel 实现 #

结合FIND、MIN、LEFT函数定位首个字母位置并提取：
假设数据在A列，在B列单元格输入以下公式（支持大小写字母）：

=LEFT(A1, MIN(FIND({"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","a","b","c","d","e","f","g","h","i","j","k","l","m","n","o","p","q","r","s","t","u","v","w","x","y","z"}, A1&"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz")))

说明：FIND 遍历查找所有字母的位置，MIN 筛选出第一个出现的字母位置，LEFT 提取该位置及之前的内容。

SQL 实现（以MySQL/PostgreSQL为例） #

MySQL #

使用REGEXP_SUBSTR函数直接匹配提取：

SELECT REGEXP_SUBSTR(your_column, '.*?[A-Za-z]') AS extracted_content
FROM your_table;

PostgreSQL #

通过SUBSTRING结合正则完成提取：

SELECT SUBSTRING(your_column FROM '.*?[A-Za-z]') AS extracted_content
FROM your_table;

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