trim string 2 places after decimal

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

trim string 2 places after decimal

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-09-09 : 10:18:07

I have data in a table (like #table1) and i would like to update it to look like the sample data in (#tableIwouldlike).
-essentially the formatting is to trim the data to leave 2 digits from the decimal (a bit like money might be shown)

Is there a way with substring to update the data to only return 2 spaces from the decimal? (sometimes there will be no decimal)

--sample SQL
create table
#table1
(
data_non_format nvarchar (30)
)

insert into #table1
select '234.2547' UNION all
select '12.12' UNION all
select '14789.254789' UNION all
select '6.9' UNION all
select '7000'

select * from #table1

create table
#tableIwouldlike
(
data_format nvarchar (30)
)

insert into #tableIwouldlike
select '234.25' UNION all
select '12.12' UNION all
select '14789.25' UNION all
select '6.90' UNION all
select '7000.00'

select * from #tableIwouldlike

drop table #table1
drop table #tableIwouldlike

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-09 : 10:28:21

[code]create table
#table1
(
data_non_format nvarchar (30)
)

insert into #table1
select '234.2547' UNION all
select '12.12' UNION all
select '14789.254789' UNION all
select '6.9' UNION all
select '7000'

select * from #table1

create table
#tableIwouldlike
(
data_format nvarchar (30)
)

insert into #tableIwouldlike
select cast(data_non_format as decimal(18,2))
from #table1
where isnumeric(data_non_format) = 1

select * from #tableIwouldlike

drop table #table1
drop table #tableIwouldlike[/code]

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-09 : 10:46:49

[code]SELECT *,
data_format = CASE WHEN CHARINDEX('.', data_non_format) <> 0
THEN left(data_non_format + '00', CHARINDEX('.', data_non_format + '00') + 2)
ELSE data_non_format + '.00'
END
FROM #table1[/code]

KH
[spoiler]Time is always against us[/spoiler]

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-09-09 : 10:56:04

Thanks Peso! - this is just what i need to be getting on.
Thanks
Dan

DLTaylor
Posting Yak Master

136 Posts

Posted - 2008-09-09 : 11:05:29

Khtan, your solution works great. and works on my strange data with no errors!
That has helped me loads!
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources