Regular expression example- FineBI Help Document FineBI Document

Recent Updates: May 10, 2022

1. Overview

When processing data, users often use regular expressions to describe certain string matching rules to filter text.

1.1 Expected effect

For example, the user needs to judge the "URL" text field. If the field is all numbers, it is marked as "Pure Number", otherwise it is marked as "Other", as shown in the following figure:

1.2 Implementation ideas

Use the REGEXP function to determine whether it is a pure number;

Then use the IF function to mark.

2. Operation steps

Sample data: scene document.xlsx

Upload sample data to FineBI.

2.1 Create a self-service dataset

Create a self-service dataset, select all the fields under the sample data, as shown in the following figure:

2.2 New column calculation

Method one:

Add the "New column" step, name it "Pure Number", enter the formula: IF(REGEXP(URL,"\\d*")=1,"Pure Number","Other"), as shown in the figure below:

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

Formula description:

Formula	Description
REGEXP(URL,"\\d*")	If the URL field is all numbers, it returns 1, otherwise it returns 0 "\d" means matching digits. If you want to use the "\" character, you need to add another backslash. So when the formula is regexp(string, "\d"), it will prompt that it is illegal, and it needs to be written as regexp(string, "\\d") * It means match the preceding sub-expression zero or more times.
IF(REGEXP(URL,"\\d*")=1,"Pure number","Other")	If the return value is 1, then output "pure number", otherwise output "other".

Formula

Description

REGEXP(URL,"\\d*")

If the URL field is all numbers, it returns 1, otherwise it returns 0

"\d" means matching digits. If you want to use the "\" character, you need to add another backslash. So when the formula is regexp(string, "\d"), it will prompt that it is illegal, and it needs to be written as regexp(string, "\\d")
* It means match the preceding sub-expression zero or more times.

IF(REGEXP(URL,"\\d*")=1,"Pure number","Other")

If the return value is 1, then output "pure number", otherwise output "other".

Method two:

You can also use this formula: IF(REGEXP(URL,"^[0-9]*$")=1,"Pure number","Other"), as shown in the figure below:

Note: The functions and fields in the formula box need to be selected by clicking the selection area on the left, and cannot be entered manually.

Formula description:

Formula	Description
(REGEXP(URL,"^[0-9]*$")	If the URL field is all numbers, it returns 1, otherwise it returns 0. "^" is the starting position of the matched input string. "[0-9]" match multiple digits, "[0-9]" match a single digit, "" matches the preceding sub-expression zero or more times; "$" Is the end position of the matched input string.
IF(REGEXP(URL,"^[0-9]*$")=1,"Pure number","Other")	If the return value is 1, then output "pure number", otherwise output "other".

Formula

Description

(REGEXP(URL,"^[0-9]*$")

If the URL field is all numbers, it returns 1, otherwise it returns 0.

"^" is the starting position of the matched input string.

"[0-9]*" match multiple digits, "[0-9]" match a single digit, "*" matches the preceding sub-expression zero or more times;

"$" Is the end position of the matched input string.

IF(REGEXP(URL,"^[0-9]*$")=1,"Pure number","Other")

If the return value is 1, then output "pure number", otherwise output "other".

The regular expression is illustrated in the following figure:

2.3 Effect view

See section 1.1 of this article for details.

Already the First

Already the Last

Helpful
Not helpful
Only read

售前咨询电话

400-811-8890转1

在线技术支持

在线QQ：800049425

热线电话：400-811-8890转2

总裁办24H投诉

热线电话：173-1278-1526

文档反馈

鼠标选中内容，快速反馈问题

鼠标选中存在疑惑的内容，即可快速反馈问题，我们将会跟进处理。

不再提示

10s后关闭

Regular expression example