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.
