Size of the field after a Convert

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)

Size of the field after a Convert

Author

Topic

Clages1
Yak Posting Veteran

69 Posts

Posted - 2006-12-05 : 13:17:59

Hi.

If you run this query below
you will see 000000123

I truncated and filled with left zeros with size 9

but if you take a look in the result panel
you will see that the size of rows is bigger than 9
whem i use this to export to TXT file iam getting a size of
14 positions with right blanks.

i have already used substring, etc and no solution until now.

any help

tks

C. Lages

declare @mov_mercadoria Numeric (9,2)
set @mov_mercadoria = 123.56

SELECT
REPLICATE('0', 9 - LEN(convert(numeric (9,0) , ROUND(@mov_mercadoria, 0, 1))))
+
convert(char(09), convert (Numeric (9,0) , ROUND(@mov_mercadoria, 0, 1)))

Kristen
Test

22859 Posts

Posted - 2006-12-05 : 13:35:05

I normally handle generating leading zeros something like:


DECLARE @mov_mercadoria Numeric (9,2)
SELECT	@mov_mercadoria = 123.56

SELECT RIGHT(REPLICATE('0', 10) + CONVERT(varchar(10), @mov_mercadoria), 10)

Kristen

Clages1
Yak Posting Veteran

69 Posts

Posted - 2006-12-05 : 13:55:25

In your case you did not truncate de value to only 123
and my problems is find out why the size is 9
and when i export to txt i get bigger field with blanks in right side

take a look at the result panel, you will see some blanks at right side

tks

c. lages

Kristen
Test

22859 Posts

Posted - 2006-12-05 : 14:18:04

If you want the value truncated then ROUND it or convert it via INT or use FLOOR() or whatever.

I suggest you try my method as a solution instead of "improving" your method.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-05 : 15:06:10

Or use STR function.

SELECT replace(STR(123.5628, 9, 2), ' ', '0')

9=number of digits
2=number of decimals

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-09 : 06:10:24

C.lages, if you use front end application, you should use Format function there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources