Is there a way to convert words like "fifteen" to "15" in postgres rather than writing a big case statement? I need it to accomodate 1-50.

CodePudding user response：

You can :

1. Store the mapping values in a dedicated table with 1 column of type integer (or even serial) and 1 column of type text
2. Create a function which will retrieve the corresponding text for a given number

see dbfiddle

CodePudding user response：

You can trick monetary type function cash_words() into doing something like that, as long as you can deal with the currency-related words and precision:

create or replace function read_number(num numeric) 
  returns text language sql immutable strict as $$
SELECT  
    lower(
        trim(
            replace(
                regexp_replace(
                    cash_words(num::money),
                    ' dollar.*',--cut out currency and fractions
                    ''
                ),
                '  ',--remove accidental double spaces
                ' ' 
            )
        )
    )
$$;

drop table if exists number_readings;
create table number_readings as 
  select num,read_number(num) as num_reading
from generate_series(0,2e5,1) as a(num);

create index on number_readings (num_reading) 
  include (num) with (fillfactor=100);

And then:

create or replace function text_to_num(txt text)
  returns int language sql immutable strict as $$
select num*(case when lower(txt) ~ '^minus.*' then -1 else 1 end)
from number_readings 
where num_reading=trim(
                    replace(
                        replace(
                            replace(
                                lower(txt),
                                ' and',
                                ''),
                            '-',
                            ' '),
                        'minus ',
                        '')
                    )
$$;

select text_to_num('one hundred fifty six thousand seven hundred forty two');
 text_to_num
-------------
      156742

select text_to_num('Minus One hundred and fifty-six thousand seven hundred');
 text_to_num
-------------
     -156700

Online demo