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 <fola...@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
> <hengkyliwand...@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.
