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....

[Jattewell]

I am trying to use the formula In-DB tool

Cast("MembershipNumber" As VarChar(20))

But the join still errors saying the 2 sides must have the same data connection string.

Sorry if this is confusing, you can probably tell I'm pretty new to this.

[MarqueeCrew]

@Jattewell,

If you PM me your email I could setup a WebEx to explore the challenge.

@jdunkerley79 you are welcome to join us too (same PM requirement)

Thanks,

Mark

[MarqueeCrew]

@Jattewell

The trick here was that you had a data source of Teradata (IN DB Connection #1) and another source of SQLServer (IN DB Connection #2) and you were attempting to join the two (2) sources in an IN-DB Join.  The error message that you received was that you could only have 1 connection string for the join.  By taking the smaller set of SQL Server data OUT of the DB and moving it IN to the larger Teradata connection, you could then join the data successfully.  Problem solved.

Thanks,

Mark