Regular Expression Example- FineBI Help Document

Last update：December 19, 2024

Overview

When processing data, regular expressions are often used to describe certain string-matching rules for text filtering.

Expected Effect

For example, you need to judge the URL text field. If all characters in a field are numbers, mark the field as Pure Number. Otherwise, mark the field as Others, as shown in the following figure.

Implementation Method

You can use the REGEXP function to judge whether the characters in a field are pure numbers.

Then you can use the IF function to make marks.

Example

Sample data: Scene Document.xlsx

Upload the sample data to FineBI.

Method One

Click Formula Column, enter Pure Numbers in Name of Added Formula Column, and enter IF(REGEXP(URL,"\\d*")=1,"Pure Number","Others"), as shown in the following figure.

The following table describes the formula.

Formula	Description
*REGEXP(URL,"\\d")**	If all characters in a URL field are numbers, the function returns 1. Otherwise, the function returns 0. \d represents matching numbers. To use the \ character, you need to add another backslash. For example, the formula *REGEXP(String,"\d")* is invalid and needs to be changed to *REGEXP(String,"\\d"), as shown in the following figure. represents matching the preceding sub-expression for zero or more times.
*IF(REGEXP(URL,"\\d")=1,"Pure Number","Others")**	If the returned value is 1, the function outputs Pure Number. Otherwise, the function outputs Others.

Formula

Description

REGEXP(URL,"\\d*")

If all characters in a URL field are numbers, the function returns 1. Otherwise, the function returns 0.

\d represents matching numbers. To use the \ character, you need to add another backslash. For example, the formula REGEXP(String,"\d") is invalid and needs to be changed to REGEXP(String,"\\d"), as shown in the following figure.
* represents matching the preceding sub-expression for zero or more times.

IF(REGEXP(URL,"\\d*")=1,"Pure Number","Others")

If the returned value is 1, the function outputs Pure Number. Otherwise, the function outputs Others.

Method Two

You can also use IF(REGEXP(URL,"^[0-9]*$")=1,"Pure Number","Others"), as shown in the following figure.

The following table describes the formula.

Formula	Description
*(REGEXP(URL,"^[0-9]$")**	If all characters in a URL field are numbers, the function returns 1. Otherwise, the function returns 0. ^ represents the beginning of the input matched string. [0-9]* represents matching multiple numbers, and [0-9] represents matching a single number. * represents matching the preceding sub-expression for zero or more times. $ represents the end of the input matched string.
*IF(REGEXP(URL,"^[0-9]$")=1,"Pure Number","Others")**	If the returned value is 1, the function outputs Pure Number. Otherwise, the function outputs Others.

Formula

Description

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

If all characters in a URL field are numbers, the function returns 1. Otherwise, the function returns 0.

^ represents the beginning of the input matched string.

[0-9]* represents matching multiple numbers, and [0-9] represents matching a single number. * represents matching the preceding sub-expression for zero or more times.

$ represents the end of the input matched string.

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

If the returned value is 1, the function outputs Pure Number. Otherwise, the function outputs Others.

The following figure explains the regular expression.