CODEWITHSUNDEEP
postgresql
Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

convert actual string and characters to numbers in PostgreSQL

Is there a way i could convert an actual string to a number in postgresql

i have record stored in db and below is an example of unique identifier found in each record.

d895774d-7b91-4adc-bfba-f1264e5aae04

I want to store this record in another database but i would want to generate another unique number for each transaction/line

Is there a way i can convert this d895774d-7b91-4adc-bfba-f1264e5aae04 to some number. like this actual numbers 13693660123082308270370273012321321312321

select 'd895774d-7b91-4adc-bfba-f1264e5aae04' as id

Upvotes: 0

Views: 328

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13049

First convert the string into a table of single characters (the from clause);
Then select the digits 'as is', a as 10, b as 11 and so on (the case expression);
Finally aggregate into a string (string_agg) skipping the - characters (where clause).

select 
    string_agg(case when c between '0' and '9' then c else (ascii(c) - 87)::text end, '') 
 from unnest(string_to_array('d895774d-7b91-4adc-bfba-f1264e5aae04', null)) c
 where c <> '-';

Result: 13895774137119141013121115111015126414510101404

  • Edit
select 
  td.date, 
  (
   select string_agg(case when c between '0' and '9' then c else (ascii(c) - 87)::text end, '') 
   from unnest(string_to_array(td.id, null)) c
   where c <> '-'
  ) as id
from table_d td;

Upvotes: 4

Related Questions