Converting numeric to varchar, zero issues

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)

Converting numeric to varchar, zero issues

Author

Topic

MuadDBA

628 Posts

Posted - 2006-07-17 : 16:50:41

I don't mean I have no issues, instead, I mean when I convert a number from numeric to varchar, I have a problem with zeros.

My numeric (18,6) data looks like this:

.300000

But when I convert it to varchar, it looks like this:

0.300000

Is there a way to indicate to SQL server that I want it to convert it to varchar exactly how it appears in nmumeric? I am using SQL 2000. Currently I am using CASE logic to trim off the @#$%% leading zero.

Thanks for the help.

nathans
Aged Yak Warrior

938 Posts

Posted - 2006-07-17 : 17:51:11

For display purposes? Do this in the presentation layer.

quote:
My numeric (18,6) data looks like this:

.300000

How do you know your data looks like that? Where do you see it without the leading zero?

declare @a numeric(18,6)
set @a = .300000

select @a
select cast(@a as varchar(10))

Nathan Skerl

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-17 : 18:17:45

A quick solution.

declare @a numeric(18,6)
set @a = .300000

select 	@a
select 	replace(convert(varchar(20), @a), '0.', '.')

~~You will have to check for condition where @a < 0~~
You will have to check for condition where @a < 1

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-17 : 18:41:51

quote:
Originally posted by khtan

A quick solution.
declare @a numeric(18,6)
set @a = .300000

select @a
select replace(convert(varchar(20), @a), '0.', '.')

You will have to check for condition where @a < 0

KH

That won't work.... what about 30.30000?

MuadDBA

628 Posts

Posted - 2006-07-17 : 20:39:28

quote:
Originally posted by nathans

For display purposes? Do this in the presentation layer.

y'know, not all of us have that option. I know it looks like that because I ran a query and that was the result.

MuadDBA

628 Posts

Posted - 2006-07-17 : 20:42:01

quote:
Originally posted by timmy

quote:
Originally posted by khtan

A quick solution.
declare @a numeric(18,6)
set @a = .300000

select @a
select replace(convert(varchar(20), @a), '0.', '.')

You will have to check for condition where @a < 0

KH

That won't work.... what about 30.30000?

Did you miss the part of his code that says check for @a < 1? Unless you meant to say -30.300000, which is true. You have to mae sure -1< @a < 1

I am doing this sort of thing now with case logic. Replace statement will be slightly less troublesome to type, thanks for that.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-17 : 21:03:19

"Did you miss the part of his code that says check for @a < 1? Unless you meant to say -30.300000, which is true. You have to mae sure -1< @a < 1"
Yes you are correct. The code came to my mind before my first cup of coffee

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-07-18 : 04:56:52

What you 'see' is also a presentation layer, Enterprise Manager/Quary Analyser (or what ever tool you use) is also a presentation layer. If you play around with SSMS and views a table with 'Open' (which is how you can add/edit/delete data) then bit fields suddenly is displayed as 'True'/'False' (and required entered the same way .. grrr), but trust me, they are still stored as 1 and 0.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-07-18 : 05:22:57

I know you said that @a will be between 1 and -1. if you did need to consider other values, greater or smaller, then this works (FYI).


set nocount on
declare @a numeric(18,6)
set @a = .300000
select 	@a
select 	case
		when @a between -1 and 1 then replace(convert(varchar(20), @a), '0.', '.')
		else convert(varchar(20), @a)
		end
set @a = 30.300000
select 	@a
select 	case
		when @a between -1 and 1 then replace(convert(varchar(20), @a), '0.', '.')
		else convert(varchar(20), @a)
		end
set @a = -30.300000
select 	@a
select 	case
		when @a between -1 and 1 then replace(convert(varchar(20), @a), '0.', '.')
		else convert(varchar(20), @a)
		end

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources