Re: Convert number to string

[Melvin Davidson <melvin6925@xxxxxxxxx>]

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@xxxxxxxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
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.