How to conver number.decimal to text

epicazo
epicazo used Ask the Experts™
on
Hello Experts,

In sql how could I convert a number to a text...

Example....
714 should be reported 714.0
715.55 should be reported 715.55
038.9       should read 038.9
434.91      should read 434.91
535.00       should read 535.00




Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
cast(yourNumericValue as Varchar(10))
or

convert(varchar(10), YourNumericValue)
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

TSQL Script as an example.

declare @val float
set @val=12.456
select @val, CAST(@val as varchar(10)) as casts, CONVERT(varchar(10),@val) as converts

Open in new window

@ashraf882,

Your one link belongs to SQL Server 2000 and second belongs to SQL Server 2008 whereas Author wants it for SQL Server 2005. However there is not a major difference in between.

BTW, here is the link for SQL Server 2005

http://msdn.microsoft.com/en-us/library/ms187928%28SQL.90%29.aspx


Top Expert 2012

Commented:
>>038.9       should read 038.9<<
Was this a typo or did everyone overlook this?

Author

Commented:
"038.9       should read 038.9" is not a typo.   This is a text field and the code 038.9 is correct.  The problem, I am having is that when it gets exported, then imported into excel, it clears the zeros.  

Sorry, would this Q have to go in excel?  
Top Expert 2012

Commented:
>>The problem, I am having is that when it gets exported, then imported into excel, it clears the zeros.  <<
That is because Excel is interpreting the data as numeric.  See here for some workarounds for a different problem that has the same solution:
PROBLEM: Why does Excel Insert NULLs into my table when I know there is good data in there?
http://www.sqldts.com/254.aspx
Top Expert 2012

Commented:
>>Sorry, would this Q have to go in excel?<<
Not necessarily, it is just that a clearer question would have avoided all the misguided comments you received.

Author

Commented:
After reviewing the data, this is what I realized.   Another table I queried contains a field [PPDRG2] with four space " 392" with leading space.  I tried converting to leading ZERO, but not sure of the sytax and I get same result.  Could somebody help me convert the ' 392' to '0392'   ?   Thanks.   I increased to points.

SELECT PPGRP#, PPPAT#, PPDRG2, REPLICATE('0', 4 - LEN(PPDRG2)) + PPDRG2 AS NewDRG
FROM  dbo.HPPATMFL
results

PPGRP#  PPPAT#  PPDRG2   NewDRG
20      7401573      0392      0392
20      7407075      0392      0392
20      7407570       392       392
20      7407810       392       392
20      7408263       392       392
20      7408693       392       392

Author

Commented:
correction....
Another table I queried contains a field [PPDRG2] with one leading space " 392"
Top Expert 2012
Commented:
Why not do a LTRIM() as in:
SELECT PPGRP#, PPPAT#, PPDRG2, REPLICATE('0', 4 - LEN(LTRIM(PPDRG2))) + LTRIM(PPDRG2) AS NewDRG
FROM  dbo.HPPATMFL

Author

Commented:
thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial