Convert String to Integer datatype | Community
Skip to main content
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by Milan_Vucetic

Hi @tejashriw155148 ,

 

you must do a split after query selection to get two branches (one for INT and another for STRING).

After that you may convert to  preferred type in Enrichment nodes in needed (per branch.)

 

If your data have only two characters you may adapt query in split:

  1. To identify numbers something like: first character in (1,2,3,4,5,6,7,8,9) and second character in (1,2,3,4,5,6,7,8,9)
  2. To identify non-numbers just enable Complement of point 1 to get all non-numbers

 

Generally, with provided data all input values are string. Then you need to separate it with split (above) and do conversion of numbers to integer.

 

Regards,

Milan

3 replies

_Manoj_Kumar_
Community Advisor
_Manoj_Kumar_Community Advisor
Community Advisor
April 28, 2020

Hello @tejashriw155148 

 

You can try ToInt64(<number>) or ToInteger(<number>) depending on your use case.

Thanks,
Manoj

     Manoj     Find me on LinkedIn
tejashriw155148
tejashriw155148Author
Level 4
April 28, 2020

Hello @_manoj_kumar_ , 

 

I tried ToInt64(<number>) but its showing Error converting datatype varchar to bigint

and for ToInteger(<number>) Conversion failed while converting the varchar value 'LG' to data type int

 

Do you have any other solution?

 

Thanks.

 

 

Darren_Bowers
Darren_Bowers
Level 9
April 28, 2020

Looking at your data below, you cant convert an alpha-based string to an integer. It has to be a numeric string only.

Hard to know what your actual data looks like with that small sample, but if you can assume that the non-numeric data will always start with an alpha character, you can do something like this:

Iif(Ascii(Left(@yourColumn,1)) < 58 and Ascii(Left(@yourColumn,1)) > 47, ToInteger(@yourColumn) , -1 )

This checks the first character to see if it is between 0 and 9. If it is then convert the value to an Integer. Otherwise put "-1" in the data.

 

    R
    rntrp
    Level 3
    May 17, 2023

    "This checks the first character to see if it is between 0 and 9. If it is then convert the value to an Integer. Otherwise put "-1" in the data."


    How to make this work for decimal values? Like 5.5 or 12.5?

    Milan_Vucetic
    Milan_VuceticAccepted solution
    Level 9
    April 30, 2020

    Hi @tejashriw155148 ,

     

    you must do a split after query selection to get two branches (one for INT and another for STRING).

    After that you may convert to  preferred type in Enrichment nodes in needed (per branch.)

     

    If your data have only two characters you may adapt query in split:

    1. To identify numbers something like: first character in (1,2,3,4,5,6,7,8,9) and second character in (1,2,3,4,5,6,7,8,9)
    2. To identify non-numbers just enable Complement of point 1 to get all non-numbers

     

    Generally, with provided data all input values are string. Then you need to separate it with split (above) and do conversion of numbers to integer.

     

    Regards,

    Milan

    Terms & ConditionsAccessibility statement

    Loading footer...