0

How to pick only numeric values from a column?

Is there a function to pick only numeric values from a column. Say in a column i have "212,34". I want to pick only the numeric value i.e, 21234. Is there a way to pick only the numbers in incorta.?

Thanks in Advance.

9replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hi Sowmya,

    I am not clear about how data is stored in a column in Incorta table in your case.

    So let me assume that 212,34 is stored as string. Your column data type is string if you have comma. If that is the case, then you can create a formula column to convert string to integer using following formula:

    int ( replace ( <schema_name>.<table_name>.amount, " , " ,"" ) )

    Reply Like
  • Thank you for your response.

    To be more precise about my question:
    The data in the column is stored in .json format and I want to pick one of the ID column value. The length of the ID value will vary as shown in example below:

    "ID":281797,"IDName"
    "ID":281,"IDName"
    "ID":2817,"IDName"

    The value I want to pick is 281797,281,2817 only.

    I used substring to achieve this:

    substring(<schema_name>.<table_name>.ID,(findLast("ID",<schema_name>.<table_name>.Col)+4),7)))

    Using substring I got the below result (It is picking value from the next column as the integer value is varying)

    281797,
    281,"ID
    2817,"I

    In the above result i want to pick only the integer value.
    Can you please help me on this.

    Thanks in advance.

    Reply Like
  • Try using:

    substring(test.t2.c1, 6, (find("IDName",test.t2.c1)-7))

    As shown in image below:

    Reply Like
  • Thank you for the solution.

    But below is the part of data in the .json format. 

    "ID":281797,"IDName"
    "ID":281,"IDName"
    "ID":2817,"IDName"

    So, I cannot use "substring(test.t2.c1, 6, (find("IDName",test.t2.c1)-7))"

    I have other parameters coming before the ID as shown below. I need to write a formula which will be dynamic to pick the value of the ID. 
    As the value before the ID will be varying as per the inputs.

    {
    "date": "2018-10-09 16:19:05 +0000",
      "ID": "check",
      "Version": "1"
    },
    {
        "Type": "connection",
        "Info": {

    },
    {
          "Type": "connect",
          "Data": {
            "url": "xyz.com"
    }
    .
    .
    .
    .
    .
    {
         "testType": "connect",
          "Data": {
            "url": "abc.com"
    },
      "location": {
        "ID": 276783,
        "IDName": Peter,
        "Address": US
      }
     

    Reply Like
  • Sowmya - how are you ingesting the json data in Incorta?

    Reply Like
  • Hi Amit,

    1.

    We have a table t1 in oracle database in which one of the column(c1) is having data in .json format and its datatype is clob. Other columns in the table are having either varchar/number/timestamp datatype.

    desc t1

    Name    Null            Type
    ------ --------------  ---------    
    c1          NOT NULL        CLOB
    c2          NOT NULL        timestamp(6)
    c3          NOT NULL        VARCHAR2(100 CHAR)
    c4                         NUMBER(1)

    2.

    To achieve this we have created a schema to pull all the details from table t1

    select * from table t1

    We got all the details of table t1 in incorta.

    Still working on how to pull the field values from column c1 by using some logic/substring..

    3. 

    The data in column C1 is as shown below.   
    We want to pull some of the field values from the below json data.
    For Eg: Here I want to pull the EmpID value (276783) The length of empid is not constant and is varying.

    {
    "date": "2018-10-09 16:19:05 +0000",
      "ID": "check",
      "Version": "1"
    },
    {
        "Type": "connection",
        "Info": {

    },
    {
          "Type": "connect",
          "Data": {
            "url": "xyz.com"
    }
    .
    .
    .
    .
    .
    {
         "testType": "connect",
          "Data": {
            "url": "abc.com"
    },
      "location": {
        "EmpID": 276783,
        "IDName": Peter,
        "Address": US
      }

    4.

    Please let us know if there is any other way to ingest json data present in oracle table to incorta.

    Thanks in advance.

    Regards,
    Sowmya

    Reply Like
  • Another way is to use the oracle functions for handling json and parse the value via sql and use it directly in the sql extract query in Incorta . Please refer to https://www.google.com/amp/s/www.red-gate.com/simple-talk/sql/oracle/json-for-absolute-beginners-part-4-retrieving-json-in-oracle/amp/

    Reply Like
  • Thanks a lot for you inputs.

    Reply Like
Like Follow
  • Status Answered
  • 11 mths agoLast active
  • 9Replies
  • 261Views
  • 3 Following