Force Leading Zeroes

30 Aug 2006 4:55 PM

Pancho_11

Hello,
I have a char(4) field with values like this:
22
6
1234
335

I want all values to get a preceding 0 if they are less than 4 characters,
to look
like this:

0022
0006
1224
0335

Searched around this forum but couldn't find a match. I can change the
field data type if necessary also. Any suggestions would be appreciated.
Thanks, P_11

30 Aug 2006 5:01 PM

SQL Menace

declare @v char(4)
select @v = 6
select right('0000' + rtrim(@v),4)

change @v to your columnname

Denis the SQL Menace
http://sqlservercode.blogspot.com/

Pancho_11 wrote:
Show quote

> Hello,
> I have a char(4) field with values like this:
> 22
> 6
> 1234
> 335
>
> I want all values to get a preceding 0 if they are less than 4 characters,
> to look
> like this:
>
> 0022
> 0006
> 1224
> 0335
>
> Searched around this forum but couldn't find a match. I can change the
> field data type if necessary also. Any suggestions would be appreciated.
> Thanks, P_11

30 Aug 2006 5:04 PM

Roy Harvey

SELECT RIGHT('0000' + ThatCol,4)
FROM Whatever

Roy Harvey
Beacon Falls, CT

On Wed, 30 Aug 2006 09:55:01 -0700, Pancho_11
<Pancho***@discussions.microsoft.com> wrote:

Show quote

>Hello,
>I have a char(4) field with values like this:
>22
>6
>1234
>335
>
>I want all values to get a preceding 0 if they are less than 4 characters,
>to look
>like this:
>
>0022
>0006
>1224
>0335
>
>Searched around this forum but couldn't find a match. I can change the
>field data type if necessary also. Any suggestions would be appreciated.
>Thanks, P_11

30 Aug 2006 5:23 PM

--CELKO--

>> I have a char(4) field [sic] with values like this:.. I want all values to get a preceding 0 if they are less than 4 characters, <<

Assuming that the digits are all to the right:

UPDATE Floob
SET foobar
        = REPLACE (foobar, ' ', '0');

Then add a constraint to prevent bad data from ever being inserted
again. If there is a logical default value, make it part of the
column. It is not good programming or plumbing to mop the floor and
not fix the leak.

foobar CHAR(4) DEFAULT '0000'
       NOT NULL
       CHECK (foobar LIKE '[0-9][0-9][0-9][0-9]'),

Defaults, constraints, data types, etc in SQL are some of the things
that make a column totally different from a field.

30 Aug 2006 5:42 PM

Barry

Celko,

Can I ask how your example works - as it doesn't seem to work for me?
Maybe I'm missing something but this example works...

Update Floob
Set foobar = RIGHT('0000' + rtrim(foobar),4)

But this does not ....

UPDATE Floob
SET foobar
= REPLACE (foobar, ' ', '0');

Thanks

Barry

30 Aug 2006 5:57 PM

Roy Harvey

My example failed to allow for foobar being declared fixed length. The
rtrim corrects that.

Roy

Show quote

On 30 Aug 2006 10:42:26 -0700, "Barry" <barry.ocon***@manx.net> wrote:

>
>Celko,
>
>Can I ask how your example works - as it doesn't seem to work for me?
>Maybe I'm missing something but this example works...
>
>Update Floob
>Set foobar = RIGHT('0000' + rtrim(foobar),4)
>
>But this does not ....
>
>UPDATE Floob
> SET foobar
> = REPLACE (foobar, ' ', '0');
>
>
>Thanks
>
>Barry

30 Aug 2006 5:32 PM

Arnie Rowland

In addition to the other suggestions, if you want to retain the leading
zeros (does that have an 'e'?), you may wish to change the column datatype
to char(4).

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Pancho_11" <Pancho***@discussions.microsoft.com> wrote in message
news:2B8FFE1B-C747-437E-9838-ED9E4710679C@microsoft.com...
> Hello,
> I have a char(4) field with values like this:
> 22
> 6
> 1234
> 335
>
> I want all values to get a preceding 0 if they are less than 4 characters,
> to look
> like this:
>
> 0022
> 0006
> 1224
> 0335
>
> Searched around this forum but couldn't find a match. I can change the
> field data type if necessary also. Any suggestions would be appreciated.
> Thanks, P_11

30 Aug 2006 6:14 PM

Pancho_11

Thanks to everyone for their suggestions today. I have collected them all
and marked them with a helpful rating. Best Regards, Pancho_11

Show quote

"Arnie Rowland" wrote:

> In addition to the other suggestions, if you want to retain the leading
> zeros (does that have an 'e'?), you may wish to change the column datatype
> to char(4).
>
>
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "Pancho_11" <Pancho***@discussions.microsoft.com> wrote in message
> news:2B8FFE1B-C747-437E-9838-ED9E4710679C@microsoft.com...
> > Hello,
> > I have a char(4) field with values like this:
> > 22
> > 6
> > 1234
> > 335
> >
> > I want all values to get a preceding 0 if they are less than 4 characters,
> > to look
> > like this:
> >
> > 0022
> > 0006
> > 1224
> > 0335
> >
> > Searched around this forum but couldn't find a match. I can change the
> > field data type if necessary also. Any suggestions would be appreciated.
> > Thanks, P_11
>
>
>