Cast/Convert Query help

microsoft.public.sqlserver.programming

29 Nov 2006 3:17 PM

Andrew

This is one of those things I can't figure out, but feel like I am missing
something very simple....

I have a table where one of the columns contains a number that is 7 or more
digits long. The whole number means one thing, but the right 6 digits are
used to mean something else. I have a query that does a Right(Field, 6) but
when the left-most digit of the 6 happens to be a zero, I get back 5 digits.
I have tried using CAST and CONVERT to change the field into a char or
varchar, but still I get back 5 digits when the left-most happens to be a
zero.

Looking at the table structure, this is what the column is set as:
[TADOC#] [decimal](9, 0) NOT NULL

An example of a "problem" number: 1001347 or 1013293

This is what my query looks like when I focus on this field alone:

SELECT Case
WHEN TADOC# IS NOT NULL THEN Right(TADOC#, 6)
ELSE TADOC#
END AS 'New_ID'
FROM SQLTRAN

When I run the query, the above example numbers turn into:
1347
13293

When I try using Right(Cast(TADOC# as varchar(10)), 6), I get the same
result.

This one I don't understand, but when I use "Right(Cast(TADOC# as char(10)),
6)" -- char instead of varchar -- I get:
347
293

I must be missing something here, so damn simple I am just not seeing my
mistake, but what I need/want to get back is
001347
013293

Can someone please help me out here?

-- Andrew