Change 9999 to read 99.99

Question

Post reply

Change 9999 to read 99.99

Hyabusact

SSC Eights!

Points: 848
More actions
November 13, 2008 at 8:50 pm

#202813

We built a table that contains a fiels called Check_Total. This holds the value of a check. We converted an old system to use a new system and left this field as a Varchar simply because the older system had mixed data types.
The problem is that we have an import script that already imports the properly formatted numbers. (Proper numbers will contain a 2-digit decimal place)
The new programmer created two newer scripts to import in data and did NOT format the text properly. So now we have a table that contains valuse without the 2-digit decimal place.
How can I change the existing data that is not formatted properly to include the decimal without adding a second decimal to the correct values.

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

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

Anam Verma SSCertifiable Points: 5365 More actions · Answer 1

you can run query below for all numbers which are not having decimal.

create table #tmp

(id varchar(10))

insert into #tmp

select '10.10'

union select '1010'

union select '20.20'

union select '2020'

select *, cast(id as float) / 100.00

from #tmp

where id not like '%.%'

vbandlamudi Mr or Mrs. 500 Points: 542 More actions · Answer 2

create table #tmp

(id varchar(10))

insert into #tmp

select '10.10'

union select '3010'

union select '20.20'

union select '4020'

union select '14020'

union select '1402'

update #tmp set id=substring(id,1,len(id)-2)+'.'+substring(id,len(id)-1,len(id)) where id not like '%.%'

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 3

anam (11/13/2008)
you can run query below for all numbers which are not having decimal.
create table #tmp
(id varchar(10))
insert into #tmp
select '10.10'
union select '1010'
union select '20.20'
union select '2020'
select *, cast(id as float) / 100.00
from #tmp
where id not like '%.%'

Ummm... no... absolutely not... you're in for a big surprise on the line for 2020 and there's nothing to force 1010 to have a trailing zero.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Slight change to the code, and this seems to work:

create table #tmp

(id varchar(10))

insert into #tmp

select '10.10'

union all select '1010'

union all select '20.20'

union all select '2020'

select *, cast(cast(id as decimal(18,2)) / 100.00 as decimal(18,2))

from #tmp

where id not like '%.%'

update #tmp set

id = cast(cast(id as decimal(18,2)) / 100.00 as decimal(18,2))

from #tmp

where id not like '%.%'

select * from #tmp

drop table #tmp

Jeff Moden SSC Guru Points: 1003907 More actions · Answer 5

Heh... I wanna piece of the check that has 16 digits to the left of the decimal place. 😛

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

Hey, so would I. I know I could have used a smaller decimal number based on the sample data, but sometimes you you just have to go with something a little bigger. You can always adjust when you have more and better info to work with.