char datatype padding

I noticed that when creating a char(5) field in SQL 2005 it places any
padding on the right of the value in there...for example I put in a value =
'50' and when I do a select on it with single quotes surrounding the value it
returns '50 ' . I was just wondering if that is a setting in SQL that can
be changed to have the padding at the front of the value instead at the end?
Or, what is a good way of getting that padding at to the front?
Thanks in advance,
John Scott.
John Scott,
It looks like you want 'numeric' formatting, so you could change from a
CHAR(5) to an INT or another appropriate numeric type and let the UI format
it for you. If you really want want you describe, however, you can:
RIGHT(' ' + RTRIM(YourColumn), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.
|||Of course, that falls apart if somebody typed leading blanks. Such as would
be the case after one edit. So...
RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.
|||Thanks for the help Russell!!
Thanks,
John Scott.
"Russell Fields" wrote:

> Of course, that falls apart if somebody typed leading blanks. Such as would
> be the case after one edit. So...
> RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
> RLF
> "John Scott" <johnscott@.despammed.com> wrote in message
> news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>
>

char datatype padding

I noticed that when creating a char(5) field in SQL 2005 it places any
padding on the right of the value in there...for example I put in a value =
'50' and when I do a select on it with single quotes surrounding the value i
t
returns '50 ' . I was just wondering if that is a setting in SQL that can
be changed to have the padding at the front of the value instead at the end?
Or, what is a good way of getting that padding at to the front?
Thanks in advance,
John Scott.John Scott,
It looks like you want 'numeric' formatting, so you could change from a
CHAR(5) to an INT or another appropriate numeric type and let the UI format
it for you. If you really want want you describe, however, you can:
RIGHT(' ' + RTRIM(YourColumn), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Of course, that falls apart if somebody typed leading blanks. Such as would
be the case after one edit. So...
RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> =
> '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Thanks for the help Russell!!
--
Thanks,
John Scott.
"Russell Fields" wrote:

> Of course, that falls apart if somebody typed leading blanks. Such as wou
ld
> be the case after one edit. So...
> RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
> RLF
> "John Scott" <johnscott@.despammed.com> wrote in message
> news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>
>

char datatype padding

I noticed that when creating a char(5) field in SQL 2005 it places any
padding on the right of the value in there...for example I put in a value = '50' and when I do a select on it with single quotes surrounding the value it
returns '50 ' . I was just wondering if that is a setting in SQL that can
be changed to have the padding at the front of the value instead at the end?
Or, what is a good way of getting that padding at to the front?
--
Thanks in advance,
John Scott.John Scott,
It looks like you want 'numeric' formatting, so you could change from a
CHAR(5) to an INT or another appropriate numeric type and let the UI format
it for you. If you really want want you describe, however, you can:
RIGHT(' ' + RTRIM(YourColumn), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> => '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Of course, that falls apart if somebody typed leading blanks. Such as would
be the case after one edit. So...
RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
RLF
"John Scott" <johnscott@.despammed.com> wrote in message
news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
>I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value
> => '50' and when I do a select on it with single quotes surrounding the value
> it
> returns '50 ' . I was just wondering if that is a setting in SQL that
> can
> be changed to have the padding at the front of the value instead at the
> end?
> Or, what is a good way of getting that padding at to the front?
>
> --
> Thanks in advance,
> John Scott.|||Thanks for the help Russell!!
--
Thanks,
John Scott.
"Russell Fields" wrote:
> Of course, that falls apart if somebody typed leading blanks. Such as would
> be the case after one edit. So...
> RIGHT(' ' + LTRIM(RTRIM(YourColumn)), 5)
> RLF
> "John Scott" <johnscott@.despammed.com> wrote in message
> news:9CFED437-37A2-4157-A6D9-BC7051AD6ED1@.microsoft.com...
> >I noticed that when creating a char(5) field in SQL 2005 it places any
> > padding on the right of the value in there...for example I put in a value
> > => > '50' and when I do a select on it with single quotes surrounding the value
> > it
> > returns '50 ' . I was just wondering if that is a setting in SQL that
> > can
> > be changed to have the padding at the front of the value instead at the
> > end?
> > Or, what is a good way of getting that padding at to the front?
> >
> >
> > --
> > Thanks in advance,
> >
> > John Scott.
>
>|||John,
Although there are ways to do this, you have to ask yourself if it is a
good idea. IMO, it is a bad idea. You would need such code whereever you
are handling the column, or you might get incorrect results. For
example, image a search query like this:
SELECT ...
FROM my_table
WHERE my_char5_column = @.val
If you simply use the standard behavior (right padding of spaces), this
will all work as expected, but if you change the formula, then it is all
up to get to get everything right (including how to handle strings that
are too long).
If you need left padding and no right padding, then you should do this
in the front-end application. If you must do it in the database, then I
suggest you create a computed column for it, just for display purposes.
For example:
ALTER TABLE my_table
ADD my_leftpadded_char5_column AS
RIGHT(SPACE(5)+RTRIM(my_char5_column),5)
HTH,
Gert-Jan
John Scott wrote:
> I noticed that when creating a char(5) field in SQL 2005 it places any
> padding on the right of the value in there...for example I put in a value => '50' and when I do a select on it with single quotes surrounding the value it
> returns '50 ' . I was just wondering if that is a setting in SQL that can
> be changed to have the padding at the front of the value instead at the end?
> Or, what is a good way of getting that padding at to the front?
> --
> Thanks in advance,
> John Scott.