Formating Column Date - Probably simple problem, please help

Hi,
I have a data table with a field as :
aField char(6) NO NULLS
The field aField is supposed to hold numbers formatted as 012033 (2
groups of 3 digits, each starting with 0).
Some of the entries in this field are formatted the right way, some
are missing 0s in front like for the number above: 1233 instead
012033.
I would like to fix this quickly by issuing a query to format all
numbers that are of length 4 (e.g. 1233) to numbers of length 6 (e.g.
012033) by adding 0s at the begining and in middle.
Can somebody help me with this?
Any help will be appreciated.I ment "Formatting Column Data" not " ... Date"
thank you
On Thu, 24 Mar 2005 19:39:45 GMT, Dino Buljubasic
<dino@.noplacelikehome.com> wrote:

>Hi,
>I have a data table with a field as :
>aField char(6) NO NULLS
>The field aField is supposed to hold numbers formatted as 012033 (2
>groups of 3 digits, each starting with 0).
>Some of the entries in this field are formatted the right way, some
>are missing 0s in front like for the number above: 1233 instead
>012033.
>I would like to fix this quickly by issuing a query to format all
>numbers that are of length 4 (e.g. 1233) to numbers of length 6 (e.g.
>012033) by adding 0s at the begining and in middle.
>Can somebody help me with this?
>Any help will be appreciated.|||Unless you constrain it, your data is going to start looking like this right
after you issue the update. Why don't you apply this formatting when you
PRESENT the data, instead of where it is stored? Otherwise, you will have
to use a trigger or a scheduled job to run this update again, every time the
data changes (insert OR update).
Anyway, this can done with an UPDATE statement and LEFT, RIGHT, SUBSTRING,
etc. However I don't think we have enough information to give you a perfect
answer. What happens if the data is 144? Is that 001044 or 014004?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:gh56415d3f7ldcg32krns1ocnsvo8f02c4@.
4ax.com...
> Hi,
> I have a data table with a field as :
> aField char(6) NO NULLS
> The field aField is supposed to hold numbers formatted as 012033 (2
> groups of 3 digits, each starting with 0).
> Some of the entries in this field are formatted the right way, some
> are missing 0s in front like for the number above: 1233 instead
> 012033.
> I would like to fix this quickly by issuing a query to format all
> numbers that are of length 4 (e.g. 1233) to numbers of length 6 (e.g.
> 012033) by adding 0s at the begining and in middle.
> Can somebody help me with this?
> Any help will be appreciated.|||Try,
select
'0' + left(ltrim(rtrim(colA)), 2) + '0' + right(ltrim(rtrim(colA)), 2)
from
table1
where
ltrim(rtrim(colA)) like '[0-9][0-9][0-9][0-9]'
go
AMB
"Dino Buljubasic" wrote:

> I ment "Formatting Column Data" not " ... Date"
> thank you
> On Thu, 24 Mar 2005 19:39:45 GMT, Dino Buljubasic
> <dino@.noplacelikehome.com> wrote:
>
>|||I just need to fix some rows that are created in invalid format (xxyy)
due to an error inside my code. I have fixed the error in code (when
I present data), but now instead of manually fixind the database
entries (xxyy to 0xx0yy) I want to do it fast.
A number can not be 144 or any other 3 digits. It is simply a 4
digits number that needs to be formated to be 6 digits like you would
do in VB with Format function as:
Format(xx, "000") & Format(yy, "000") = 0xx0yy.
To summarize, the data is always goint to be a number consisting of 2
parts xx and yy. Each of these is a 2 digit number. I need to make
it 3 digit number by appending 0 in front and then concatanating these
two to get 0xx0yy.
Thank you
On Thu, 24 Mar 2005 14:46:26 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>Unless you constrain it, your data is going to start looking like this righ
t
>after you issue the update. Why don't you apply this formatting when you
>PRESENT the data, instead of where it is stored? Otherwise, you will have
>to use a trigger or a scheduled job to run this update again, every time th
e
>data changes (insert OR update).
>Anyway, this can done with an UPDATE statement and LEFT, RIGHT, SUBSTRING,
>etc. However I don't think we have enough information to give you a perfec
t
>answer. What happens if the data is 144? Is that 001044 or 014004?|||UPDATE table
SET aField = '0'+LEFT(LTRIM(aField),2)+'0'+RIGHT(aFie
ld,2)
WHERE LEN(LTRIM(aField))=4
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
news:vj664195prmsmjtvqsv7vfdv0saut8tgde@.
4ax.com...
> I just need to fix some rows that are created in invalid format (xxyy)
> due to an error inside my code. I have fixed the error in code (when
> I present data), but now instead of manually fixind the database
> entries (xxyy to 0xx0yy) I want to do it fast.
> A number can not be 144 or any other 3 digits. It is simply a 4
> digits number that needs to be formated to be 6 digits like you would
> do in VB with Format function as:
> Format(xx, "000") & Format(yy, "000") = 0xx0yy.
> To summarize, the data is always goint to be a number consisting of 2
> parts xx and yy. Each of these is a 2 digit number. I need to make
> it 3 digit number by appending 0 in front and then concatanating these
> two to get 0xx0yy.
> Thank you
>
> On Thu, 24 Mar 2005 14:46:26 -0500, "Aaron [SQL Server MVP]"
> <ten.xoc@.dnartreb.noraa> wrote:
>
right
have
the
SUBSTRING,
perfect
>|||If I am not mistaken he is using a char(6) field and then you would need
rtrim not ltrim am I right'
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23HnNuxKMFHA.1176@.TK2MSFTNGP12.phx.gbl...
> UPDATE table
> SET aField = '0'+LEFT(LTRIM(aField),2)+'0'+RIGHT(aFie
ld,2)
> WHERE LEN(LTRIM(aField))=4
> --
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Dino Buljubasic" <dino@.noplacelikehome.com> wrote in message
> news:vj664195prmsmjtvqsv7vfdv0saut8tgde@.
4ax.com...
> right
> have
> the
> SUBSTRING,
> perfect
>|||Thanks Aaron,
It works fine, just needed to fix the last part:
Instead '0' + RIGHT(aField, 2) needs to be:
'0' + RIGHT(RTRIM(aField), 2)
It does exactly what I needed. I appreciate your help.
On Thu, 24 Mar 2005 15:02:09 -0500, "Aaron [SQL Server MVP]"
<ten.xoc@.dnartreb.noraa> wrote:

>UPDATE table
> SET aField = '0'+LEFT(LTRIM(aField),2)+'0'+RIGHT(aFie
ld,2)
> WHERE LEN(LTRIM(aField))=4|||Yep, I think so... I'm used to just doing LTRIM(RTRIM())...
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Denis" <denis.gobo@.gmail.com> wrote in message
news:#lzSO2KMFHA.2464@.TK2MSFTNGP10.phx.gbl...
> If I am not mistaken he is using a char(6) field and then you would need
> rtrim not ltrim am I right'
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:%23HnNuxKMFHA.1176@.TK2MSFTNGP12.phx.gbl...
time
>