Question

Pattern Match to return Number value without spaces or decimals

Workflow Version 10 Pattern Matching and Regular Expressions

Updated October 31, 2019

asked on October 30, 2019

I need to return a number value that should be updated to a SQL table, but the input can be in different formats. For example the input I receive could be :

- 12500000

- 1 2500 000

- 12500000.00

- 1 2500 000.00

I need to strip out any space or decimal and drop anything after the decimal if it exists. so from the example above I only need the value to be 12500000.

If my input is only a value with spaces then I managed to get it working with Pattern matching and assign the value to a new token using [\d]*, but the problem with this is when there is a decimal, it removes the decimal and adds the 2 digits at the end.

Any ideas?

0 0

Answer

SELECTED ANSWER

replied on October 31, 2019

Hi Dustin,

Thank you for the info and detailed explanation on how it is put together. This really helped.

There seems to be some weird behaviour though when using the suggested regex. For some reason, this regex does not like it when the first 3 number are 999.

See below:

So I opted to go for a variation of Michael's post, which seems to do the work.

See below:

Really appreciate the assistance and info.

0 0

Replies

replied on October 30, 2019

Maybe something like:

([\d\.]*)\s*([\d\.]*)\s*([\d\.]*)\s*([\d\.]*)

Using the \. tells it to include the period, the parenthesis tells it what part it wants and the * allows for 0 or more characters of that type. The \s stands for any white space. This expression would remove a maximum of three groups of spaces, but can add to it as needed.

2 0

replied on October 30, 2019

This should be what you need:

(?<Num>[\d\s]*)\k<Num>(\d*)\s*(\d*)\s*(\d*)\s*(\d*)

This expands on Michael's answer to remove the spaces, but also removes the decimal and anything following the decimal, which I believe it what you were looking to do. To break down this expression:

The "(?<Num>[\d\s]*)" is a named capture group that I've named 'Num' that will capture all of the numbers and whitespaces before the decimal, if there is one. This part first excludes the decimal and anything following it.

Examples:

1 2500 00 = 1 2500 00

12 500 00.00 = 12 500 00

1250 000.0000 = 1250 000

Then we use "\k<Num>" to recall that named capture group, which has the possible decimal and anything following it removed. We call up that previous capture, then scrub it for spaces using:

(\d*)\s*(\d*)\s*(\d*)\s*(\d*)

...which will remove up to 3 spaces from the value.

** CAUTION: If there are more than 3 spaces in the result, it will start to remove numbers from the end of the capture!! **

If you want to configure it to remove more than 3 spaces, simply add another "\s*(\d*)" to the end of the above.

Examples:

(\d*)\s*(\d*)\s*(\d*)\s*(\d*) - removes 0-3 spaces

(\d*)\s*(\d*)\s*(\d*)\s*(\d*)\s*(\d*) - removes 0-4 spaces

(\d*)\s*(\d*)\s*(\d*)\s*(\d*)\s*(\d*)\s*(\d*) - removes 0-5 spaces

(\d*)\s*(\d*)\s*(\d*)\s*(\d*)\s*(\d*)\s*(\d*)\s*(\d*) - removes 0-6 spaces

..... etc. .....

1 0

SELECTED ANSWER

replied on October 31, 2019

Hi Dustin,

Thank you for the info and detailed explanation on how it is put together. This really helped.

There seems to be some weird behaviour though when using the suggested regex. For some reason, this regex does not like it when the first 3 number are 999.

See below:

So I opted to go for a variation of Michael's post, which seems to do the work.

See below:

Really appreciate the assistance and info.

0 0

You are not allowed to follow up in this post.

Question

Question

Pattern Match to return Number value without spaces or decimals

Answer

Replies

Sign in to reply to this post.