Results 1 to 5 of 5

Thread: Scrutinize a data field to determine whether it contains alpha numeric data

  1. #1

    Default Scrutinize a data field to determine whether it contains alpha numeric data

    I have an output pin with 4 columns and about 1 million records.
    One of the columns (unit_address field) contains a data string with 8-16 characters. I would like to scrutinize that data field (unit_address) to determine whether it contains any letters. if it does contain at least one letter, i want to output it to a different output on a filter pin.

    Example:
    if unit_address field contains this data "000000194737AB36", I would like to have it output to pin 1 and taged as an alphanumeric data.
    if unit_address field contains this data "0001102639416036", I would like to have it output to pin 2 and taged as an numeric data.

    Looking for the simplest way to do this with consideration to the record count being over a million.

    Any suggestions/guidance would be greatly appreciated.

    thanks,
    Rich B

  2. #2
    Lavastorm Employee stonysmith's Avatar
    Join Date
    Nov 2006
    Location
    Grapevine Tx
    Posts
    799

    Default

    Add a Filter.
    Add a second output pin.
    Use this code:

    output 1 {
    emit *
    where not isDigit(unit_address)
    }
    output 2 {
    emit *
    where isDigit(unit_address)
    }

  3. #3
    Lavastorm Employee
    Join Date
    Apr 2014
    Location
    London
    Posts
    57

    Default

    In addition to Stony's excellent response, you could also use a Split Node and enter the following in the PredicateExpr field:-

    isDigit(unit_address)

    This option doesn't require the definition of a second output pin.

  4. #4
    Lavastorm Employee
    Join Date
    Apr 2014
    Location
    London
    Posts
    57

    Default

    Just to clarify further, isDigit requires the source field to be a string; you can also use the isNumber operator which evaluates any expression. There are also string operators for isAlpha and isAlphaNum which evaluate for alpha and alpha numeric strings accordingly.

    Ta

  5. #5

    Default

    The "isDigit" format worked out nicely. My exact syntax (inserted below) removed the preceding 0000 in the unit_address field for any record that qualified as a digit.

    output 1 {
    emit *
    where not isDigit(UNIT_ADDRESS)
    }
    output 2 {
    emit ACCOUNT_NUMBER, DIVISION, REGION, MARKET, SERIAL_NUMBER, DEVICE_MODEL, VIP, substr(UNIT_ADDRESS, 4,16) as UNIT_ADDRESS
    where isDigit(UNIT_ADDRESS)
    }


    thanks a bunch.
    Rich B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •