Converting a string to a number

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

string to number.yxmd

Regex

Preparation

Accepted answers

JohnJPS

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

jdunkerley79

Tried a simplified expression using regex_replace:

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

string to number.yxmd

All comments

JohnJPS

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

Tried a simplified expression using regex_replace:

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

string to number.yxmd

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.

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335