How to trim numeric values - SQL Server Forums

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

SQL Server Development (2000)

How to trim numeric values

Author

Topic

veparala
Starting Member

30 Posts

Posted - 2007-02-13 : 16:52:10

Hi

I would like to trim numeric values. i.e i have numeric data with datatype(10,0). But i would like to take only 5 numbers from that numeric value.

Example

If the value in the numeric field is 8294724724. I would like to get only first 5 numbers. i.e 829472.

Anybody help me on this?.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 18:11:47

[code]
declare @n numeric(10,0)
select @n = 8294724724
select left(@n, 5) -- returns a varchar type
select convert(numeric(10,0), left(@n, 5)) -- convert back to numeric
[/code]

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-13 : 22:56:32

also you can just use integer arithmetic:

select cast(8294724724/10000 as int)

www.elsasoft.org

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-13 : 23:06:34

quote:
Originally posted by jezemine

also you can just use integer arithmetic:

select cast(8294724724/10000 as int)

www.elsasoft.org

Why i didn't think of that

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-02-14 : 10:15:42

you were just throwing a bone to a poor guy with only 400 posts...

www.elsasoft.org

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-14 : 10:42:10

Maybe not all numbers have ten digits?

declare @t table (a numeric(10,0))

insert	@t
select	8294724724 union all
select	234242 union all
select	35903 union all
select	34238490

select	a,
	floor(a / power(10, floor(log(a) / log(10)) - 4))
from	@t

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources