Simple split function - 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

General SQL Server Forums

New to SQL Server Programming

Simple split function

Author

Topic

aoriju
Posting Yak Master

156 Posts

Posted - 2010-07-22 : 02:55:13

I have a numeric value 123.453
I want to get 123 in one int variable and 453 in another int variable

Regards
Riju A.O

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-22 : 03:02:16

[code]
declare @num numeric(10,3)

select @num = 123.453

select int1 = convert(int, @num),
int2 = convert(int, replace(convert(varchar(10), @num - convert(int, @num)), '0.', ''))
[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-22 : 03:05:59

does your number always have 3 decimal places ?

what happen if the numeric value is 123.045 or 123.04 ? What is the expected result ?

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

aoriju
Posting Yak Master

156 Posts

Posted - 2010-07-22 : 03:28:54

Ok thks

Kristen
Test

22859 Posts

Posted - 2010-07-22 : 04:12:04

Is the string conversion a bit OTT?



DECLARE @MyNumbers	TABLE
(
	MyNum numeric(10,4)
)
INSERT INTO @MyNumbers
SELECT	123.4535 UNION ALL
SELECT	-123.4535 UNION ALL
SELECT	123.04535 UNION ALL
SELECT	123.045 UNION ALL
SELECT	123.04

SELECT	MyNum, CONVERT(int, MyNum), CONVERT(int, (MyNum - ROUND(MyNum, 0)) * 1000.0)
FROM	@MyNumbers

MyNum                                
------------ ----------- ----------- 
123.4535     123         453
-123.4535    -123        -453
123.0454     123         45
123.0450     123         45
123.0400     123         40

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-22 : 10:57:31

quote:

Originally posted by Kristen

Is the string conversion a bit OTT?



DECLARE @MyNumbers	TABLE
(
	MyNum numeric(10,4)
)
INSERT INTO @MyNumbers
SELECT	123.4535 UNION ALL
SELECT	-123.4535 UNION ALL
SELECT	123.04535 UNION ALL
SELECT	123.045 UNION ALL
SELECT	123.04

SELECT	MyNum, CONVERT(int, MyNum), CONVERT(int, (MyNum - ROUND(MyNum, 0)) * 1000.0)
FROM	@MyNumbers

MyNum                                
------------ ----------- ----------- 
123.4535     123         453
-123.4535    -123        -453
123.0454     123         45
123.0450     123         45
123.0400     123         40

How about this?

SELECT MyNum, parsename(MyNum,2),parsename(MyNum,1)
FROM @MyNumbers

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources