Converting a string to a number

[MarqueeCrew]

I just responded to a client asking me for help.  I'm going to post my solution as an attachment, but wanted to A) See if someone had a better way to solve it and B) post the solution here for others who might search for it in the future.

I am trying to convert the text ‘numbers’  to real numbers but cannot figure out how to preserve the decimals and the negative number.  I’ve tried using the various options in the Select tool &  tried the ToNumber function & tried various formulas all to no avail.  I did find that if I reduce the number of leading zero’s the Double on the Select tool works, but it will not work when there are 25 leading zeros.  

At this point I thought I would use pattern matching to establish positive or negative;  reduce the number of leading zeros, then get it to a number using the Double in the Select tool and then reapply the negative or positive. Am I headed in the right direction or is there a better way of tackling this?

I did think that he was headed in the right direction.  Here is a browse which illustrates the initial data (Field1 & Field2).  N_Field# represents what happens if you just try to convert to a Double.  W_Field# applies a little logic and gets to the desired results.

Thanks all....

[JohnJPS · Accepted solution]

I like your approach and can think of nothing better.  You can also avoid the RegEx if desired: 

IIF(left([Field1],1) == '-',
    ToNumber('-'+Trim(Replace([Field1],"-",""),"0")),
    ToNumber(Trim([Field1],"0"),1)
)

[MarqueeCrew]

Thanks John.  I didn't think about using a constant ''-" and skipping the regex.  That should work too!

[jdunkerley79 · Accepted solution]

Tried a simplified expression using regex_replace:

tonumber(regex_replace([Field1],"^(-?)0+([1-9][0-9.]*)",'$1$2'))

[MarqueeCrew]

@jdunkerley79,

I like that too!

[Jattewell]

Hi Guys,

I have a similar question but cannot get this to work using in database tools.

I have 2 seperate databases that I am trying to join, both contain a 'membership number' to join on. One database holds this field as a V_String and the other as a FixedDecimal. I don't care which one I convert but they need to match so that I can join on them.

Anyone have a solution for this in DB?

Thanks in advance

[MarqueeCrew]

What database are you using?

[Jattewell]

Sorry should have included that. The database where the membership number is a fixeddecimal is a Teradata database and the V_String is SQL Server. Also the membership numbers are of varying length

[jdunkerley79]

I would think easiest approach would be to do a cast in the Connect In Db statement when you read the data in or to use a formula in db tool to do the same.

This will kill the performance on big data sets as the database wont be able to use the indices to help.

[jdunkerley79]

If the data set is in two different databases then you will need to bring into Alteryx and then you can use a Select Tool or Formula Tool to convert the type

I would suggest using a Formula Tool to convert the decimal to a string so you can control the format you need to match the entries in the string.

After which the join should be fine.