CODEWITHSUNDEEP
sqlpostgresql
Wei Lin
Wei Lin

Reputation: 3811

Is it possible to get varchar's unique number value?

Sample:

select 'test1' 
union all
select 'test2'

Expected output:

value
5XXXXXXX94
5XXXXXXXX6

What I have tried:

I tried using md5 to do it, but it's not number type and it's possible duplicate.

select md5('test1')
union all
select md5('test2')

Postgres 11 Demo Link | db<>fiddle

Upvotes: 0

Views: 52

Answers (2)

Volkan Albayrak
Volkan Albayrak

Reputation: 439

Edit : I've change the function db<>fiddle

with x as (
select 'test1' as word
union all
select 'test2'
union all
select 'test2'
union all
select 'test2'
union all
select 'test1'
union all
select 'test1'
)
select
    x.word,
    (select ('x'||substr(md5(x.word),1,16))::bit(64)::bigint)
from x;

You can use regexp_split_to_table https://dbfiddle.uk/?rdbms=postgres_11&fiddle=8866ff2fba92d45a9d19ee0fb24168aa

with x as (
select 'test1' as word
union all
select 'test2'
union all
select 'test2'
union all
select 'test2'
union all
select 'test1'
union all
select 'test1'
)
select
    x.word,
    (select sum(ascii(regexp_split_to_table)) from regexp_split_to_table(x.word, ''))
from x;

Unfortunately it is not unique. It only calculates sum of ascii numbers of given word.

Upvotes: 1

Akina
Akina

Reputation: 42739

Create a table which will hold the relation between a string and a code. Create function which accepts the string, searches it in the table, if not found then generates and inserts unique random (or next using some generator) code, and then returns the code (found or generated).

Or create a function which generates some hand-made "hash". For example, it may get code of each char in a string and perform some deterministic calculations with it. In this case you do not need in relational table. Or you may use CRC32 converting it to decimal form (just get up to 10 digits).

Upvotes: 1

Related Questions