| Author |
Leading Zero's Quick Help!!!
|
|
| Eric Lovelace 2006-01-24, 11:23 am |
| I have a employees table that has there employee number. How to I update the
empnum field so that it pads zero's and every employee number is 6 digits?
An example would be 256 would become 000256
Thanks and an A+
| |
|
| select RIGHT('000000'+conve
rt(varchar, ColumnNameHere, 6)
--
HTH. Ryan
"Eric Lovelace" < EricLovelace@discuss
ions.microsoft.com> wrote in message
news:72CE9A8D-E709-4CC6-B6E7- 01B7DD88AE53@microso
ft.com...
>I have a employees table that has there employee number. How to I update
>the
> empnum field so that it pads zero's and every employee number is 6 digits?
> An example would be 256 would become 000256
>
> Thanks and an A+
| |
| Rick Sawtell 2006-01-24, 11:23 am |
|
"Eric Lovelace" < EricLovelace@discuss
ions.microsoft.com> wrote in message
news:72CE9A8D-E709-4CC6-B6E7- 01B7DD88AE53@microso
ft.com...
>I have a employees table that has there employee number. How to I update
>the
> empnum field so that it pads zero's and every employee number is 6 digits?
> An example would be 256 would become 000256
>
> Thanks and an A+
You could do something like:
SELECT RIGHT('000000' + RTRIM('256'), 6)
Rick Sawtell
MCT, MCSD, MCDBA
| |
| Eric Lovelace 2006-01-24, 11:23 am |
| Thanks for the quick reply.
I get this message when I try to run
Line 1: Incorrect syntax near ')'.
"Ryan" wrote:
> select RIGHT('000000'+conve
rt(varchar, ColumnNameHere, 6)
>
>
> --
> HTH. Ryan
> "Eric Lovelace" < EricLovelace@discuss
ions.microsoft.com> wrote in message
> news:72CE9A8D-E709-4CC6-B6E7- 01B7DD88AE53@microso
ft.com...
>
>
>
| |
| Steve L 2006-01-24, 11:23 am |
| Mix the two above, the second one will only be valid if the employee
number is already a VARCHAR, if it is an int - you will have to convert
the column or add another column to accept the VARCHAR back into it to
keep the leading 0's that you require.
Steve L
Dominicus Data Systems
| |
| Eric Lovelace 2006-01-24, 11:23 am |
| Great thanks that works for the select. Now what about the update?
I tried this but it doesn't pad them
UPDATE leaders
SET ldr_empnum = RIGHT('000000' + RTRIM(ldr_empnum), 6)
"Rick Sawtell" wrote:
>
> "Eric Lovelace" < EricLovelace@discuss
ions.microsoft.com> wrote in message
> news:72CE9A8D-E709-4CC6-B6E7- 01B7DD88AE53@microso
ft.com...
>
> You could do something like:
>
> SELECT RIGHT('000000' + RTRIM('256'), 6)
>
>
>
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
>
>
| |
|
| I should have tested before posting... Here you go..
select RIGHT('000000'+conve
rt(varchar, ColumnNameHere), 6) FROM tablename
--
HTH. Ryan
"Eric Lovelace" < EricLovelace@discuss
ions.microsoft.com> wrote in message
news:51C00961-166C-4435-8FDA- 964AA76A1F5F@microso
ft.com...[color=darkred]
> Thanks for the quick reply.
> I get this message when I try to run
>
> Line 1: Incorrect syntax near ')'.
>
>
> "Ryan" wrote:
>
| |
| Eric Lovelace 2006-01-24, 11:23 am |
| Thanks guys this worked!!!
UPDATE leaders
SET ldr_empnum = RIGHT('000000'+conve
rt(varchar, ldr_empnum), 6)
"Steve L" wrote:
> Mix the two above, the second one will only be valid if the employee
> number is already a VARCHAR, if it is an int - you will have to convert
> the column or add another column to accept the VARCHAR back into it to
> keep the leading 0's that you require.
>
> Steve L
> Dominicus Data Systems
>
>
| |
|
| What is the datatype of ldr_empnum ...? If it's int it will not retain the
leading zero's you will need to make it char,varchar etc
--
HTH. Ryan
"Eric Lovelace" < EricLovelace@discuss
ions.microsoft.com> wrote in message
news:944CB082-4993-44F8-858E- 525C1ED711B5@microso
ft.com...[color=darkred]
> Great thanks that works for the select. Now what about the update?
> I tried this but it doesn't pad them
>
> UPDATE leaders
> SET ldr_empnum = RIGHT('000000' + RTRIM(ldr_empnum), 6)
>
> "Rick Sawtell" wrote:
>
|
|
|
|