Problem in string - 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)

Problem in string

Author

Topic

abhi143
Starting Member

32 Posts

Posted - 2006-07-20 : 02:39:27

I have a money type data type field.
Suppose 123456, now i want to convert it .123456

How to do it.???

Suppose

12 = .12
1234 = .1234

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-20 : 02:49:44

Will this do ?

select	mny, convert(money, '.' + replace(convert(varchar(20), mny), '.', ''))
from
(
	select	convert(money, 12) mny	union all
	select	convert(money, 1234) mny
) m

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-07-20 : 17:21:21

How about this:

DECLARE @num int
SET @num = 123456

SELECT @num / CONVERT(decimal(12, 2), POWER(10, LEN(@num)))

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-21 : 07:55:29

Or this...

I think this will be more efficient than going via varchar, but you need to be careful with rounding issues. nosepicker - this is similar to yours, but starts with the money datatype.

--data
declare @t table (v money)
insert @t
          select 12
union all select 1234
union all select 123456
union all select 9999
union all select 999.999
union all select 999999
union all select 0.9999

--calculation
select v * power(0.100000, round(log10(v)+0.5, 0)) from @t

/*results
--------------------- 
.120000
.123400
.123456
.999900
.999999
.999999
.999900
*/

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

gugarc
Starting Member

17 Posts

Posted - 2006-07-21 : 09:01:36

Don´t forget that the money data type has a scale of 4 (only four digits after the decimal digit) - whatever you do, you may loose precision in the decimal numbers (at least, when more then 4) ...

abhi143
Starting Member

32 Posts

Posted - 2006-07-24 : 03:48:02

Hello All of u..

please note that iam getting bug in all the syntac given by you...

please review below mentioned example..

when i used this no...

01234 = .1234 (Wrong)
it should be .01234(correct)

help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-24 : 03:58:20

what is the source data type for 01234 ?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 04:10:06

Do you just want to show for the presentation or you want to save this format in the Database?

Chirag

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-24 : 04:15:05

can you have 01234 as money data type???
my guess you're just testing the script using values you input not based on the table?

quote:

I have a money type data type field.

--------------------
keeping it simple...

abhi143
Starting Member

32 Posts

Posted - 2006-07-24 : 04:18:39

Let it be..String data type..

in that case, ho to handle this??????????

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-24 : 04:22:31

Will this do ?

select convert(money, '.' + '01234')
select convert(decimal(10,5), '.' + '01234')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources