extract the number before %

Question

Post reply

extract the number before %

Frances L

SSCarpal Tunnel

Points: 4727
More actions
January 10, 2011 at 6:57 am

#230269

83%
20%
67%
90%
60%
100%
I have the data like above. I need to get the number before % out. How do I accomplish this ? Thanks.
in the asp, I use this intScore = left(trim(Request.Form("percentage")), instr(trim(Request.Form("percentage")), "%")-1)
to get the score out?
Thx.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Would a simple REPLACE(YourValue,'%','') do the trick?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 2

yes.

select convert( int, (replace(score, '%','') )) as TScore from [Quiz log]

right now I need to just extract the TScore >80 . but when I

tried

select convert( int, (replace(score, '%','') )) as TScore from [Quiz log] where TScore >80

it gave me error as TScore as invalid column name.

I tried this way. I do not know whether there is a more elegant way to accomplish this.

select convert( int, (replace(score, '%','') )) as TScore from [Quiz log] where Score<>'Waived' and convert( int, (replace(score, '%','') ))>80

mazzz SSCertifiable Points: 6139 More actions · Answer 3

You could just query on WHERE score > '80%'

"greater than" will be calculated alphabetically here. Presuming your data is as one would expect, ie values between 0% and 100%

Not sure how well it would work with decimals though

Edit: seems to work okay with decimals

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 4

well , I think if there 100%. then it will different. Thx.

mazzz SSCertifiable Points: 6139 More actions · Answer 5

Frances L (1/10/2011)
well , I think if there 100%. then it will different. Thx.

True. Trust me to worry about decimals and miss the obvious 🙂

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

mazzz (1/10/2011)
Frances L (1/10/2011)
well , I think if there 100%. then it will different. Thx.
True. Trust me to worry about decimals and miss the obvious 🙂

Did you also check it for single digit percentages?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 7

I do not think I need to worry about single digit since I convert to int already. Thx.

mazzz SSCertifiable Points: 6139 More actions · Answer 8

Jeff Moden (1/10/2011)
mazzz (1/10/2011)
Frances L (1/10/2011)
well , I think if there 100%. then it will different. Thx.
True. Trust me to worry about decimals and miss the obvious 🙂
Did you also check it for single digit percentages?

Good point. It would appear my brain neglected to return from the holidays at the same time as the rest of me did.

------------------------------------------------------------------------
Bite-sized fiction (with added teeth) [/url]

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 9

I am in the same mental state, too. Go back to work.Cheer.