之前在官方网站看过一篇讲解正则表达式的英文文章,当时看了感觉甚好,只是没有特别好的场景应用。今天在客户这里遇到了一个问题,正好想起来,于是重读,写一下最基本的使用方法。 文件详见: Become a regex regular & wrangle imperfect data

我是一个经世致用的人,不愿意在形式和炫技上浪费一点时间,更希望能借助数据来解决实用的问题。

有一种场景是数据整理的“天敌”,就是你的数据混乱到不做整理完全无法使用,特别是喜欢用爬虫的人尤其如此。今天我在客户这里见到一个数据,大概率是客户的客户从其他渠道整理来的数据,因此混乱的很。

在计算机的世界,有一种特别聪明的方式称之为:正则表达式,英文是Regular Expressions,简称RegEx。它可以根据我们设置的规则,去查询或者返回特定的字符,比如返回从a到z的任意字符,可以用[a_z]来代替——注意是任意一个字母。那如果要返回ab两个字母呢?可以通过大括号设置匹配的数量,比如[a-z]{2}代表返回任意两个从a到z的字母的两位。

Tableau中提供了四种正则表达式:

理解的关键是“模式”,指的是一组特定条件的字符组合,比如从a到z,从A到Z,从0到9 等等。“模式”和单一的字符对应,是可以变化的一组条件。 ( regex,  regex as “a pattern describing a certain amount of text” )

模式用一个特别的符号来表示——方括号,比如用[A_Z]代表从A到Z的任意字母,[1234]代表从1、2、3、4这四个数之一等等。为什么用方括号呢?熟悉python的朋友都知道,方括号在很多地方都代表集合。

  • 方括号:用于查找某个范围内的字符
  • 小括号:用于提取你想要返回的字符模式
  • 大括号:正则匹配的字符数量​
表达式 描述
[abc] 查找方括号之间的任何字符。
[^abc] 查找任何不在方括号之间的字符。
[0-9] 查找任何从 0 至 9 的数字。
[a-z] 查找任何从小写 a 到小写 z 的字符。
[A-Z] 查找任何从大写 A 到大写 Z 的字符。
[A-z] 查找任何从大写 A 到小写 z 的字符。
[adgk] 查找给定集合内的任何字符。
[^adgk] 查找给定集合外的任何字符。
(red|blue|green) 查找任何指定的选项。

1、REGEXP_EXTRACT(string, pattern)——常用

第一个函数用于提取符合特定条件的部分,比如我们从一组混乱的数据中提取电子邮件地址,提取11位的电话号码。这是最常用的正则函数。结合我自己的一个案例,说明如下:

比如我这里有很多的期权合约代码,格式一般为 CF009C12400.ZCE,分别代表“品种(CF)”、“合约(009)”、“看涨C/看跌P”、“行权价格(12400)”、分隔符(.)、交易所(ZCE)。

每个品种、合约、行权价格的位数都不确定,因此不能使用left、right和mid字符串函数来拆分,不过可以使用已知的模式来返回特定的字符串部分。

  • 品种和合约:一位或者两位字母+三位或者四位数字;
  • 涨跌及行权价格:C或者P开始+数字,以分隔符(.)结尾
  • 交易所:分隔符(.)之后的字母,两位至四位。

因此,品种和合约的正则表达式如下:

截屏2019-12-21下午12.50.45.png

‘ ([A-Z]{1,2}+[0-9]{3,4}) ‘

大括号中的数字,代表匹配的位数,因此字母可以是一位(M1707),也可以是两位(CU2003);同样数字可以是三位,也可以是四位。返回的结果如下:

截屏2019-12-21下午1.02.11.png

那,如果除了开头,其中还有符合这个规律的,我要提取如何处理?

截屏2019-12-21下午1.14.21.png

我们可以增加条件,但是保持返回的范围不变。

第二组符合条件的字符串可以加一个特征:跟紧在数字0-9之后,这个特征是第一组字符串没有的。表达式如下:

截屏2019-12-21下午1.17.31.png

当然,这里的字母只有C或者P两个可能,因此也可以把([A-Z]{1,2} 改为:(C |P )

REGEXP_EXTRACT([ts_code],'[0-9]+((C|P)+[0-9]{3,4})’)

new:

今天客户提了一个需求,SAP的数据中经常有“前导0”,也就是10位的字符串,不足10位的数字前面都用0补齐,但是这样不适合我们搜索,因此需要把前面的0删除。

这是一个明显具有一定模式的字符串,而且难以用其他方式解决。

模式是,前面是多个0,紧跟一个数字1~9,然后后面是任意数字的任意组合。

这个模式结构用这样的字符代替: [0]+[1-9]+[0-9]

不过上面没有代表每个的数量,用大括号代表数量,增加如下:

[0]{1,}+[1-9]+[0-9]{1,}

这里的{1,}代表1位或者高于1位的任意数量。 

我们想要返回的是 后面的部分,因此,把后面的用括号包围,如下:

[0]{1,}+ ( [1-9]+[0-9]{1,}  )

完整的字段如下:

REGEXP_EXTRACT( [field], ‘ [0]{1,}+ ( [1-9]+[0-9]{1,}  )   ‘)

完美解决!

 

2、REGEXP_REPLACE (字符串, 模式, 替换字符串)   正则匹配后替换

Tableau提供了一个常见的替换方式,replace([string],’A’,’new’) ,就是把string字符串中的A替换为new。

正则匹配的替换,和这个replace有些类似,但是它适用的场景更多,比如除了A,还想要把B,C,F,Z也替换为new如何?我们可以使用正则匹配把上面的‘A’替换为'[A-Z]’ , 使用下面的正则匹配替换,就可以把所有的从A到Z的字符,全部替换为new字符串。

Regexp_Replace([string],  ‘[A-Z]’,  ‘new’ )

这里的关键是,一方面要保留单引号,证明这是一个字符串,另一方面使用 方括号,指多个字符之一——  ‘[A-Z]’ 

那如果我们不仅仅是 [A-Z],还想要小写的 [0-9],那怎么办呢?正则匹配可以同时写多个范围,因此可以改为下面的样式:

Regexp_Replace([string],  ‘[A-Z][0-9]’,  ‘new’ )

再则,还有一些特别的字符,我们也可以使用正则来匹配,比如把逗号、引号、短横,都改为新字符,每一个特殊字符,都可以加上一个斜线来代表,比如 – 代表短横, \” 代表双引号。

Regexp_Replace([string],  ‘[A-Z][0-9] \- \” \,,  ‘new’ )

3、REGEXP_MATCH(字符串, 模式)

如果我们要查找在一个字符串中是否包含了一个特征的字符,比如 string字段中是否包含’A’,可以使用Tableau内置的contain函数,如下:

Contain( [string], ‘A’ )  如果返回包含字符A,返回 true(是)

和上面的正则匹配替换类似,如果我们把需求扩展一下,查找符合匹配条件的某一些字符串,比如只要是包含A到Z的任意一个字符,即返回true,刚才的contain函数就需要增加正则匹配,这里使用了Regexp_match( )

Regexp_match ( [string], ‘[A_Z]’  )

总结Tableau中正则表达式的关键:

  • 匹配的模式,或者说条件用单引号包围;
  • 要返回的部分,使用括号包围,适用于REGEXP_EXTRACT函数;
  • 范围用[中括号]包围,代表这个集合内的任意一个值;
  • 如果要匹配多个数值,使用{大括号}指定数量。

 

喜乐君  Dec 21, 2019
增加一个 例子,使用了 {1,} Jan 8, 2020