4 Replies Latest reply on Jul 12, 2018 3:35 PM by Stephen Hicks

# Numeric String Field Verification

I've got a dimension whose domain consists of 11-character strings. I need to create a calculation that verifies each character as an integer. Ideally it would be like a KPI sum, saying:

Entries:                            401,883

Entries w/11 characters:  401,883

Entries w/only integers:   401,883

The LEN() function will get me the 11 character verification, but I don't have any idea how I can parse each individual character to test for integer vs non-integer.  Any ideas?

• ###### 1. Re: Numeric String Field Verification

Just do

INT([string-field])

If all numeric, then it will return a value.  If not all numeric, it will return null.

You can embed that, something like this:

SUM( IF NOT ISNULL(INT([string])) then 1 else 0 END )

That sum will give you the number of entries that are numeric.

1 of 1 people found this helpful
• ###### 2. Re: Numeric String Field Verification

By golly, Joe!

You dun did it again! Brilliant. My mind doesn't even work like that.

I Just had to comment on it.

• ###### 3. Re: Numeric String Field Verification

This worked great!

I found another way, though it's a bit more complicated. I used the RAWSQL_BOOL() function (first time I've given that a try), and it looked like this:

RAWSQL_BOOL("ISNUMERIC (%!) <> 0",[string])

This brought back the value "True" for every field row that was indeed numeric.  Fun exercise, and it's always refreshing to get a new function to work for the first time!

• ###### 4. Re: Numeric String Field Verification

Here you go!

LEN(REGEXP_REPLACE([Seat From Num],'^[0-9]*\$',''))=0

null = null

False = Alphanumeric

True = Numeric

Works by removing all numbers to see if you end up with nothing left