Thread: Convert number to string

---

This email has been checked for viruses by Avast antivirus software. www.avast.com

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 ?

 

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.