From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>, Hengky Liwandouw <hengkyliwandouw(at)gmail(dot)com> |
Subject: | Fwd: Convert number to string |
Date: | 2015-09-24 09:35:17 |
Message-ID: | CA+bJJbwQeFGEEsVrhhZ7iNGxA-kKsjmXtxhtie_Tqpayx+ZOqA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)gmail(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | maxiangqian | 2015-09-24 09:55:50 | Use tar to online backup has an error |
Previous Message | John R Pierce | 2015-09-24 08:16:26 | Re: Convert number to string |