Mark Forums Read

weird question

Go Back

comp.databases.postgresql comp.databases.postgresql

Reply

Page 1 of 2

1

#1

Old

Coniglio Sgabbiato

Posts: n/a

weird question - 04-22-2008 , 04:22 AM

Hi, due to compatibility with legacy database codes I need to catenate
two numeric fields filling the gaps with zeros. For example take a table
with two numeric(5,0) fields, I need to catenate it in this way:

fld_a fld_b wanted_result
123 12345 0012312345
54 123 0005400123
5432 1234 0543201234

I am thinking about something like:

select repeat('0', 5-length(cast(fld_a as varchar))) || fld_a from tbl

but it is a little bit ugly, does someone has a smart, elegant, and
straightforward solution?

Reply With Quote

#2

Old

Andreas Kretschmer

Posts: n/a

Re: weird question - 04-22-2008 , 04:57 AM

begin Coniglio Sgabbiato schrieb:

Quote:

Hi, due to compatibility with legacy database codes I need to catenate
two numeric fields filling the gaps with zeros. For example take a table
with two numeric(5,0) fields, I need to catenate it in this way:

fld_a fld_b wanted_result
123 12345 0012312345
54 123 0005400123
5432 1234 0543201234

I am thinking about something like:

test=*# select * from n;
a | b
------+-------
123 | 12345
54 | 123
5432 | 1234
(3 rows)

test=*# select to_char(a,'FM09999')||to_char(b,'FM09999') as "wanted result" from n;
wanted result
---------------
0012312345
0005400123
0543201234
(3 rows)

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Reply With Quote

#3

Old

Andreas Kretschmer

Posts: n/a

Re: weird question - 04-22-2008 , 04:57 AM

begin Coniglio Sgabbiato schrieb:

Quote:

Hi, due to compatibility with legacy database codes I need to catenate
two numeric fields filling the gaps with zeros. For example take a table
with two numeric(5,0) fields, I need to catenate it in this way:

fld_a fld_b wanted_result
123 12345 0012312345
54 123 0005400123
5432 1234 0543201234

I am thinking about something like:

test=*# select * from n;
a | b
------+-------
123 | 12345
54 | 123
5432 | 1234
(3 rows)

test=*# select to_char(a,'FM09999')||to_char(b,'FM09999') as "wanted result" from n;
wanted result
---------------
0012312345
0005400123
0543201234
(3 rows)

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Reply With Quote

#4

Old

Andreas Kretschmer

Posts: n/a

Re: weird question - 04-22-2008 , 04:57 AM

begin Coniglio Sgabbiato schrieb:

Quote:

Hi, due to compatibility with legacy database codes I need to catenate
two numeric fields filling the gaps with zeros. For example take a table
with two numeric(5,0) fields, I need to catenate it in this way:

fld_a fld_b wanted_result
123 12345 0012312345
54 123 0005400123
5432 1234 0543201234

I am thinking about something like:

test=*# select * from n;
a | b
------+-------
123 | 12345
54 | 123
5432 | 1234
(3 rows)

test=*# select to_char(a,'FM09999')||to_char(b,'FM09999') as "wanted result" from n;
wanted result
---------------
0012312345
0005400123
0543201234
(3 rows)

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Reply With Quote

#5

Old

Andreas Kretschmer

Posts: n/a

Re: weird question - 04-22-2008 , 04:57 AM

begin Coniglio Sgabbiato schrieb:

Quote:

Hi, due to compatibility with legacy database codes I need to catenate
two numeric fields filling the gaps with zeros. For example take a table
with two numeric(5,0) fields, I need to catenate it in this way:

fld_a fld_b wanted_result
123 12345 0012312345
54 123 0005400123
5432 1234 0543201234

I am thinking about something like:

test=*# select * from n;
a | b
------+-------
123 | 12345
54 | 123
5432 | 1234
(3 rows)

test=*# select to_char(a,'FM09999')||to_char(b,'FM09999') as "wanted result" from n;
wanted result
---------------
0012312345
0005400123
0543201234
(3 rows)

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Reply With Quote

#6

Old

Andreas Kretschmer

Posts: n/a

Re: weird question - 04-22-2008 , 04:57 AM

begin Coniglio Sgabbiato schrieb:

Quote:

Hi, due to compatibility with legacy database codes I need to catenate
two numeric fields filling the gaps with zeros. For example take a table
with two numeric(5,0) fields, I need to catenate it in this way:

fld_a fld_b wanted_result
123 12345 0012312345
54 123 0005400123
5432 1234 0543201234

I am thinking about something like:

test=*# select * from n;
a | b
------+-------
123 | 12345
54 | 123
5432 | 1234
(3 rows)

test=*# select to_char(a,'FM09999')||to_char(b,'FM09999') as "wanted result" from n;
wanted result
---------------
0012312345
0005400123
0543201234
(3 rows)

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Reply With Quote

#7

Old

Andreas Kretschmer

Posts: n/a

Re: weird question - 04-22-2008 , 04:57 AM

begin Coniglio Sgabbiato schrieb:

Quote:

Hi, due to compatibility with legacy database codes I need to catenate
two numeric fields filling the gaps with zeros. For example take a table
with two numeric(5,0) fields, I need to catenate it in this way:

fld_a fld_b wanted_result
123 12345 0012312345
54 123 0005400123
5432 1234 0543201234

I am thinking about something like:

test=*# select * from n;
a | b
------+-------
123 | 12345
54 | 123
5432 | 1234
(3 rows)

test=*# select to_char(a,'FM09999')||to_char(b,'FM09999') as "wanted result" from n;
wanted result
---------------
0012312345
0005400123
0543201234
(3 rows)

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Reply With Quote

#8

Old

Andreas Kretschmer

Posts: n/a

Re: weird question - 04-22-2008 , 04:57 AM

begin Coniglio Sgabbiato schrieb:

Quote:

Hi, due to compatibility with legacy database codes I need to catenate
two numeric fields filling the gaps with zeros. For example take a table
with two numeric(5,0) fields, I need to catenate it in this way:

fld_a fld_b wanted_result
123 12345 0012312345
54 123 0005400123
5432 1234 0543201234

I am thinking about something like:

test=*# select * from n;
a | b
------+-------
123 | 12345
54 | 123
5432 | 1234
(3 rows)

test=*# select to_char(a,'FM09999')||to_char(b,'FM09999') as "wanted result" from n;
wanted result
---------------
0012312345
0005400123
0543201234
(3 rows)

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Reply With Quote

#9

Old

Andreas Kretschmer

Posts: n/a

Re: weird question - 04-22-2008 , 04:57 AM

begin Coniglio Sgabbiato schrieb:

Quote:

Hi, due to compatibility with legacy database codes I need to catenate
two numeric fields filling the gaps with zeros. For example take a table
with two numeric(5,0) fields, I need to catenate it in this way:

fld_a fld_b wanted_result
123 12345 0012312345
54 123 0005400123
5432 1234 0543201234

I am thinking about something like:

test=*# select * from n;
a | b
------+-------
123 | 12345
54 | 123
5432 | 1234
(3 rows)

test=*# select to_char(a,'FM09999')||to_char(b,'FM09999') as "wanted result" from n;
wanted result
---------------
0012312345
0005400123
0543201234
(3 rows)

end
Andreas
--
Andreas Kretschmer
Linux - weil ich es mir wert bin!
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net

Reply With Quote

#10

Old

Coniglio Sgabbiato

Posts: n/a

Re: weird question - 04-29-2008 , 06:55 AM

Andreas Kretschmer ha scritto:

Quote:

begin Coniglio Sgabbiato schrieb:
Hi, due to compatibility with legacy database codes I need to catenate
two numeric fields filling the gaps with zeros. For example take a table
with two numeric(5,0) fields, I need to catenate it in this way:

fld_a fld_b wanted_result
123 12345 0012312345
54 123 0005400123
5432 1234 0543201234

I am thinking about something like:

test=*# select * from n;
a | b
------+-------
123 | 12345
54 | 123
5432 | 1234
(3 rows)

test=*# select to_char(a,'FM09999')||to_char(b,'FM09999') as "wanted result" from n;
wanted result
---------------
0012312345
0005400123
0543201234
(3 rows)

end
Andreas

Thanks!, I did not know formatting functions in PG

Reply With Quote

Reply

Page 1 of 2

1

« Previous Thread | Next Thread »

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.