asked on

how do extract just the numeric part of a string from a column

Hi,

I have a table that looks like this;

Col A
Example 2.0000%-3.0000%
Example2 3.0000%-4.5000%

so basically in the example Col a has a numeric value up to 4dp in the string. I would like to strip all the zeros in the name whenever there are zeros at the end of the number;

e.g 2.0000%-3.0000% will become 2%-3%
3.0000%-4.5000% will become 3%-4.5%

Anyone know how i can do this please.
thks.

ASKER CERTIFIED SOLUTION

grant300

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

colechr

ASKER

thank you very much for your response. This more or less gives me the format i need.
However i just have one problem. when i convert @F1 and @F2 back into a string again i get the wrong format again.

e,g

declare @F1ANDF2 varchar(255)

set @F1ANDF2 = str(@F1,20,4) + str(@F2,20,4)

gives me 2.0000%=3.2500%

how do i convert this back to a string and get the format 2%-3.25% ?

thks

grant300

First, you have to make sure that @F1 and @F2 are of datatype FLOAT otherwise this doesn't work.

Second, don't use the STR() function. Use the CONVERT(<datatype>,<value>) function instead. That way you have finer control over the conversions.

Finally, the way you have the STR() function coded, you are asking for 4 decimal places to the right and that is what you are getting.

Regards,
Bill