convert percent stored as varchar to small int

Question

Post reply

convert percent stored as varchar to small int

Frances L

SSCarpal Tunnel

Points: 4727
More actions
May 26, 2006 at 9:33 am

#117741

I want to conver the following varchar into 100 or 80 or 70 etc. Thanks.
   100%
       100%
       100%
       80%
       90%
       80%
       90%
       80%
       30%
       90%
       100%

       70%

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

i searched for "strip function" and found this link:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=12594

Antarese686 contributed this function:

CREATE FUNCTION dbo.fn_StripAlpha (@val VARCHAR(8000))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @valout VARCHAR(8000)

DECLARE @pos INT

SET @pos = 0

SET @valout = ''

WHILE @pos < DataLength(@val)

BEGIN

SET @pos = @pos + 1

IF ISNUMERIC(SUBSTRING(@val, @pos, 1)) = 1

BEGIN

SET @valout = @valout + SUBSTRING(@val, @pos, 1)

END

RETURN @valout

END

so you could do a select (convert(float,(dbo.fn_StripAlpha(percentfield)) as percentstripped

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

David McFarland SSChampion Points: 11815 More actions · Answer 2

For a quick and dirty option, just use something like:

SELECT

David McFarland SSChampion Points: 11815 More actions · Answer 3

That didn't work well, so we'll try again

SELECT intNewField = Cast(Replace(vchOldField,'%','') AS int)

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

Thanks. Right now I use

select cast(left(score,len(score)- 1) as smallint) from [Doctor Quiz Log]

David McFarland SSChampion Points: 11815 More actions · Answer 5

The only reason I would recommend my method over your's, Frances, is on the chance that some of the entries don't have a percent sign.

With both of our code, 100% would end up 100, but with your's 100 would end up as 10, while mine would still remain 100.

You know your data better than I, so it might not be an issue.

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

Frances L

SSCarpal Tunnel

Points: 4727

May 26, 2006 at 10:22 am

#640383

Thanks.