convert actual string and characters to numbers in PostgreSQL

Question

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

remove the dashes, then convert the hex value to [a number](https://stackoverflow.com/questions/8316164/convert-hex-in-text-representation-to-decimal-number) — , Mar 17 '21 at 19:23

Stefanov.sm · Accepted Answer · 2021-03-18T10:41:33.743

4

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;

edited Mar 18 '21 at 10:41

answered Mar 17 '21 at 18:15

Stefanov.sm

11,215
2
21
21

Nice. how do i incorporate this in my query `select date, id from table_d` – Omari Victor Omosa Mar 18 '21 at 08:13
Thanks, that one will do – Omari Victor Omosa Mar 18 '21 at 10:45
1

@OmariVictorOmosa: if you need that a lot, you should create a function out of that to make your life easier – Mar 18 '21 at 10:57

convert actual string and characters to numbers in PostgreSQL

1 Answers1