Convert number to string
Hi,
I don't have any idea how can I use postgres function to convert series of
number (currency) to my own string.
My string : F G H I J K L M N as the replacement for number : 1 2 3
4 5 6 7 8 9
Dozens = O
Hundreds = P
Thousands = C
Ten thousands = B
So.
200 = GP
2000 = GC
1150 = FFJO
30000 = HB
Any idea ?
Thanks In advance
---
This email has been checked for viruses by Avast antivirus software.
https://www.avast.com/antivirus
On 9/24/2015 1:09 AM, Hengky Liwandouw wrote:
Hi,
I don’t have any idea how can I use postgres function to convert
series of number (currency) to my own string.My string : F G H I J K L M N as the replacement for number :
1 2 3 4 5 6 7 8 9Dozens = O
Hundreds = P
Thousands = C
Ten thousands = B
So…
200 = GP
2000 = GC
1150 = FFJO
30000 = HB
Any idea ?
going from FFJO -> 1150 is simple character substitution (assuming
'dozens' actually means tens, and not its traditional meaning of 12s).
going the other way, thats probably a good excuse for a plperl function.
--
john r pierce, recycling bits in santa cruz
Forgot replying to all, forwarding to the list, sorree for the potential dupes.
Hi Hengky:
On Thu, Sep 24, 2015 at 10:09 AM, Hengky Liwandouw
<hengkyliwandouw@gmail.com> wrote:
I don’t have any idea how can I use postgres function to convert series of number (currency) to my own string.
My string : F G H I J K L M N as the replacement for number : 1 2 3 4 5 6 7 8 9
Dozens = O
Hundreds = P
Thousands = C
Ten thousands = B
So…
200 = GP
2000 = GC
1150 = FFJO
30000 = HB
Any idea ?
Your example es severely unspecified, how do you translate 1024? and
1002? and 100000?
given the numbers in the example you can use a simple trick. 1st
replace using O for 0 via translate ( I'm not using capitals in teh
exmaple for better 0-o contrast ):
# with data(s) as (values('200'),('2000'),('1150'),('30000')) select
translate(s,'0123456789','ofghijklmn') from data;
translate
-----------
goo
gooo
ffjo
hoooo
(4 rows)
then replace sequences of 'o' starting with the longer ones:
# with source(s) as (values('200'),('2000'),('1150'),('30000'))
, step1(s) as (select translate(s,'0123456789','ofghijklmn') from source)
, step2(s) as (select replace(s,'oooo','b') from step1)
, step3(s) as (select replace(s,'ooo','c') from step2)
, step4(s) as (select replace(s,'oo','p') from step3)
select * from step4;
s
------
gp
gc
ffjo
hb
(4 rows)
clasical trick. But, as I said above, you need to specify it much better.
Francisco Olarte.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Import Notes
Reply to msg id not found: CA+bJJbz3k=kvJQn9R3_PDowGCzhA1hQZSgbKHpB_wYsRCySwrw@mail.gmail.com
Postgresql has a translate function:
So you can create a pgsql function that
A;: converts the numeric to text: txtnum := 12345::text
B, Translate it. eg: transnum := SELECT translate(txtnum, '123456789',
'FGHIJKLMN');
eg SELECT translate('31241', '12345', 'FGHIJ'); = HFGIF
You would then need a little further processing to determine the tens,
hundreds, etc.
I'll leave that to you to work out, but additional functions of
strpos(string, substring)
substr(string, from [, count])
length(string)
would seem helpful
http://www.postgresql.org/docs/9.2/interactive/functions-string.html
On Thu, Sep 24, 2015 at 5:35 AM, Francisco Olarte <folarte@peoplecall.com>
wrote:
Forgot replying to all, forwarding to the list, sorree for the potential
dupes.Hi Hengky:
On Thu, Sep 24, 2015 at 10:09 AM, Hengky Liwandouw
<hengkyliwandouw@gmail.com> wrote:I don’t have any idea how can I use postgres function to convert series
of number (currency) to my own string.
My string : F G H I J K L M N as the replacement for number : 1
2 3 4 5 6 7 8 9
Dozens = O
Hundreds = P
Thousands = C
Ten thousands = B
So…
200 = GP
2000 = GC
1150 = FFJO
30000 = HB
Any idea ?
Your example es severely unspecified, how do you translate 1024? and
1002? and 100000?given the numbers in the example you can use a simple trick. 1st
replace using O for 0 via translate ( I'm not using capitals in teh
exmaple for better 0-o contrast ):# with data(s) as (values('200'),('2000'),('1150'),('30000')) select
translate(s,'0123456789','ofghijklmn') from data;
translate
-----------
goo
gooo
ffjo
hoooo
(4 rows)then replace sequences of 'o' starting with the longer ones:
# with source(s) as (values('200'),('2000'),('1150'),('30000'))
, step1(s) as (select translate(s,'0123456789','ofghijklmn') from source)
, step2(s) as (select replace(s,'oooo','b') from step1)
, step3(s) as (select replace(s,'ooo','c') from step2)
, step4(s) as (select replace(s,'oo','p') from step3)
select * from step4;
s
------
gp
gc
ffjo
hb
(4 rows)clasical trick. But, as I said above, you need to specify it much better.
Francisco Olarte.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
HI Melvin:
On Thu, Sep 24, 2015 at 2:51 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:
Postgresql has a translate function:
Did you read the ( quoted at the bottom of the reply you sent ) message
you were replying to? It contained a working sample using exactly this
translate. ;->
You would then need a little further processing to determine the tens,
hundreds, etc.I'll leave that to you to work out, but additional functions of
strpos(string, substring)
substr(string, from [, count])
length(string)
Do not forget replace, shorter, easier.
Francisco Olarte.