helpp...urgent - 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

helpp...urgent

Author

Topic

jhon11
Starting Member

42 Posts

Posted - 2008-01-10 : 14:44:16

i want haveing a problem with conversion...

I have values like 234.567 , 33.567
and i want to display only 3 significat digits.
means

234.567= 235
3.456=3.46
12.567=12.6

can anybody help out.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-01-10 : 14:54:10

Left(FieldName,3)

Poor planning on your part does not constitute an emergency on my part.

jhon11
Starting Member

42 Posts

Posted - 2008-01-10 : 15:17:32

if i am doing

'$'+cast(left(AVG(totalpaid),3)as varchar(50)) as 'avg cost'

its give me error msg

Server: Msg 257, Level 16, State 3, Line 33
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-10 : 15:47:39

I adapted some code from [url]http://sqlblogcasts.com/blogs/rob_farley/articles/829.aspx[/url] for your needs:

DECLARE @Digits INT
DECLARE @Yak TABLE (Number DECIMAL(10, 4))

INSERT @Yak
SELECT 234.567
UNION ALL SELECT 3.456
UNION ALL SELECT 12.567
UNION ALL SELECT 12345.7898

SET @Digits = 3

SELECT 
	CASE
		WHEN CHARINDEX('.', CAST(CASE WHEN Number = 0 then 0 ELSE ROUND(Number, @Digits - 1 - FLOOR(LOG10(ABS(Number)))) END AS VARCHAR(11))) < 4
			THEN LEFT(CAST(CASE WHEN Number = 0 then 0 ELSE ROUND(Number, @Digits - 1 - FLOOR(LOG10(ABS(Number)))) END AS VARCHAR(11)), 4)  
		ELSE
			LEFT(CAST(CASE WHEN Number = 0 then 0 ELSE ROUND(Number, @Digits - 1 - FLOOR(LOG10(ABS(Number)))) END AS VARCHAR(11)), CHARINDEX('.',CAST(CASE WHEN Number = 0 then 0 ELSE ROUND(Number, @Digits - 1 - FLOOR(LOG10(ABS(Number)))) END AS VARCHAR(11))) - 1)
	END AS Digit
FROM @Yak

EDIT: added update to handle numbers with more than 3 digits on the left side of teh decimal place.

jhon11
Starting Member

42 Posts

Posted - 2008-01-10 : 16:06:43

thx for all your help..

but..any of this not workig....

if i use left(field,3) will give me like 23. , 456, like wierd result....i dont want that kind of results

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-10 : 16:22:07

quote:
Originally posted by jhon11

I have values like 234.567 , 33.567
and i want to display only 3 significat digits.
means

234.567= 235
3.456=3.46
12.567=12.6

The results of my query produce:

235
3.46
12.6

Have your requirements changed or did you not try my suggestion?

jhon11
Starting Member

42 Posts

Posted - 2008-01-10 : 16:23:12

but i have like 30000 record in that column..so i can t use union all for all those

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-10 : 16:30:39

quote:
Originally posted by jhon11

but i have like 30000 record in that column..so i can t use union all for all those

Wow... Maybe this will help??

Replace <Your Number Column> with the name of the column from the table you are working with (totalpaid?) and change <Your Table Name> to the name of teh table you are selecting from:

SELECT 
	CASE
		WHEN CHARINDEX('.', CAST(CASE WHEN <Your Number Column> = 0 then 0 ELSE ROUND(<Your Number Column>, @Digits - 1 - FLOOR(LOG10(ABS(<Your Number Column>)))) END AS VARCHAR(11))) < 4
			THEN LEFT(CAST(CASE WHEN <Your Number Column> = 0 then 0 ELSE ROUND(<Your Number Column>, @Digits - 1 - FLOOR(LOG10(ABS(<Your Number Column>)))) END AS VARCHAR(11)), 4)  
		ELSE
			LEFT(CAST(CASE WHEN <Your Number Column> = 0 then 0 ELSE ROUND(<Your Number Column>, @Digits - 1 - FLOOR(LOG10(ABS(<Your Number Column>)))) END AS VARCHAR(11)), CHARINDEX('.',CAST(CASE WHEN <Your Number Column> = 0 then 0 ELSE ROUND(<Your Number Column>, @Digits - 1 - FLOOR(LOG10(ABS(<Your Number Column>)))) END AS VARCHAR(11))) - 1)
	END AS Digit
FROM <Your Table Name>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources