-2

I am using a profiling tool to validate the data inside the tables. I want to check to see if the data in the tables matches the requirement of checking to see if the values input for current market value amount usd are length size of 22 and decimal size of 3. I am using ataccamba profiling tool which picks up the variables.

iif( matches(@"^\d{22}.\d{3}$", Current_Market_Value_Amount__USD_), true,false

I am looking to make this validation rule satisfy the requirement of:

Current Market Value Amount (USD) attribute should be in numeric format with length size of 22 and decimal size of 3

George Halford
  • 29
  • 7
  • Your edit is on the good track! Could you add a bit of code context (which language, how is declared `Current_Market_Value_Amount__USD_` and so on) – Thomas Ayoub Mar 18 '16 at 13:28
  • Also, if you get an error message, it's better if you tell us what it says instead of *this returns an error* ;) – Thomas Ayoub Mar 18 '16 at 13:32

2 Answers2

0

You don't need more than:

^\d{22}\.\d{3}$

Since \d matches a digit, number inside bracket ensure that the preceding item is repeated x times (or from m to n in case of {m,n}).

By the way, Regex are not supported inside formula, I suggest you to take a look at How do I get regex support in excel via a function, or custom function?

Community
  • 1
  • 1
Thomas Ayoub
  • 29,063
  • 15
  • 95
  • 142
  • iif( matches(@"^\d{22}\.\d{3}$", Current_Market_Value_Amount__USD_), true,false – George Halford Mar 18 '16 at 13:11
  • @GeorgeHalford see edit. Next time, you should precise that you want to do this inside a formula – Thomas Ayoub Mar 18 '16 at 13:16
  • I am using a profiling tool which shows up the variables I am checking to see whether the data matches the requirement if it does not return false – George Halford Mar 18 '16 at 13:19
  • you should [edit](http://stackoverflow.com/posts/36085166/edit) your question, explaining a lot more what you're trying to do, in which context and then we'll be able to help you @GeorgeHalford – Thomas Ayoub Mar 18 '16 at 13:22
  • iif( matches(@"^([\d]|1[0,1,2])/([0-9]|[0,1,2][0-9]|3[0,1])/\d{4}$", Maturity_Date), true,false ) – George Halford Mar 18 '16 at 13:28
  • is the formula I used to check date format and this works successfully – George Halford Mar 18 '16 at 13:28
  • error received: Syntax error, invalid token , expected ')', at 79 in 'iif( matches(@"^\d{22}.\d{3}$", Current_Market_Value_Amount__USD_), true,false'. Function 'iif' is not applicable for arguments {} at 1-3 in 'iif( matches(@"^\d{22}.\d{3}$", Current_Market_Value_Amount__USD_), true,false'. – George Halford Mar 18 '16 at 13:40
  • @GeorgeHalford please, when adding information **edit your post**. So, gather this information from the comments, edit your post, remove all theses comment and I'll be glad to help you – Thomas Ayoub Mar 18 '16 at 13:43
0

You need to escape the ., by doing \. because it is a meta-character that has the meaning 'match any'. To mean the literal . you prefix it with the \ escape character.

On a side note: your [0-9] repetitions can be replaced easily:

^[0-9]{18}\.[0-9]{3}$

This assumes the total string length must be 22.

wvdz
  • 16,251
  • 4
  • 53
  • 90