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

user330315
– user330315

2021-03-17 19:23:11 +00:00
Commented Mar 17, 2021 at 19:23 — user330315
– user330315, Commented Mar 17, 2021 at 19:23

Stefanov.sm · Accepted Answer · 2021-03-18 10:41:33Z

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, 2021 at 10:41

answered Mar 17, 2021 at 18:15

Stefanov.sm

13.3k2 gold badges25 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Omari Victor Omosa Over a year ago

Nice. how do i incorporate this in my query select date, id from table_d

Omari Victor Omosa Over a year ago

Thanks, that one will do

user330315 Over a year ago

@OmariVictorOmosa: if you need that a lot, you should create a function out of that to make your life easier

Collectives™ on Stack Overflow

convert actual string and characters to numbers in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related