Regular Expression Example- FineBI Document

Last update： 2022-05-10

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.

Helpful
Not helpful
Only read

中文（简体）中文（繁體）日本語

English

Regular Expression Example