Padding with zeros

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Padding with zeros

Brian Johnson-2
I need to update a char field to add integer prefixes, but I need to pad them
with zeroes so I can sort them.

Can't google any info on how to do this.

Could be a mixture of bash and sqlite sql since I want to occasionally run it
from the command line

Reply | Threaded
Open this post in threaded view
|

Re: Padding with zeros

Igor Tandetnik
Brian Johnson wrote:
> I need to update a char field to add integer prefixes, but I need to
> pad them with zeroes so I can sort them.

I'm not exactly sure what you are trying to do, but this would perhaps
give you an idea:

update table set field=substr('00...0' || field, -20, 20)

assuming you want your fields padded to 20 characters wide. Here
'00...0' is a string consisting of 20 '0' characters.

Igor Tandetnik

Reply | Threaded
Open this post in threaded view
|

Re: Re: Padding with zeros

Jay Sprenkle
Have you looked at the collating sequence options?
perhaps you can get it to sort how you want without the padding.

On 12/29/05, Igor Tandetnik <[hidden email]> wrote:
> Brian Johnson wrote:
> > I need to update a char field to add integer prefixes, but I need to
> > pad them with zeroes so I can sort them.
>
Reply | Threaded
Open this post in threaded view
|

Re: Re: Padding with zeros

Brian Johnson-2
In reply to this post by Brian Johnson-2
I have a field of text and I want to prefix the that text with numbers padded
with zeroes.

eg currently
text 1
text 2
text 3

to become
001 text 1
002 text 3
003 text 2

or ultimately
b001 text 1
b002 text 3
b003 text 2


Igor Tandetnik ([hidden email]) wrote:

>
> Brian Johnson wrote:
> > I need to update a char field to add integer prefixes, but I need to
> > pad them with zeroes so I can sort them.
>
> I'm not exactly sure what you are trying to do, but this would perhaps
> give you an idea:
>
> update table set field=substr('00...0' || field, -20, 20)
>
> assuming you want your fields padded to 20 characters wide. Here
> '00...0' is a string consisting of 20 '0' characters.
>
> Igor Tandetnik
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Re: Padding with zeros

Brian Johnson-2
In reply to this post by Brian Johnson-2
where can I find info about this?

Jay Sprenkle ([hidden email]) wrote:

>
> Have you looked at the collating sequence options?
> perhaps you can get it to sort how you want without the padding.
>
> On 12/29/05, Igor Tandetnik <[hidden email]> wrote:
> > Brian Johnson wrote:
> > > I need to update a char field to add integer prefixes, but I need to
> > > pad them with zeroes so I can sort them.
> >
>
>

Reply | Threaded
Open this post in threaded view
|

Re: Re: Padding with zeros

Nathan Kurz
In reply to this post by Brian Johnson-2
On Fri, Dec 30, 2005 at 12:21:05AM +0000, Brian Johnson wrote:

> I have a field of text and I want to prefix the that text with numbers padded
> with zeroes.
>
> eg currently
> text 1
> text 2
> text 3
>
> to become
> 001 text 1
> 002 text 3
> 003 text 2
>
> or ultimately
> b001 text 1
> b002 text 3
> b003 text 2

While it is possible you really need to do it the way you said, might
it work to just have a separate column in the database that is used
for the sorting?   Then do something like:

create table your_table (text_column, sort_column);
select text_column from your_table order by sort_column;

(very unchecked syntax, but you get the idea)

--nate
Reply | Threaded
Open this post in threaded view
|

Re: Re: Padding with zeros

Jay Sprenkle
In reply to this post by Brian Johnson-2
http://www.google.com/search?hl=en&q=sqlite+collate&btnG=Google+Search


On 12/29/05, Brian Johnson <[hidden email]> wrote:

> where can I find info about this?
>
> Jay Sprenkle ([hidden email]) wrote:
> >
> > Have you looked at the collating sequence options?
> > perhaps you can get it to sort how you want without the padding.
> >
> > On 12/29/05, Igor Tandetnik <[hidden email]> wrote:
> > > Brian Johnson wrote:
> > > > I need to update a char field to add integer prefixes, but I need to
> > > > pad them with zeroes so I can sort them.
> > >
> >
> >
>
>


--
---
The Castles of Dereth Calendar: a tour of the art and architecture of
Asheron's Call
http://www.lulu.com/content/77264