英文:
Convert alphanumeric string to integer in Redshift
问题
我有一个包含多列主键的表。出于ETL作业的目的，我需要将所有主键列连接到一个单独的列中进行去重。
假设我的组合主键是：
jesse_20230431_low
james_20230431_high
有没有办法将其转换为保持唯一性的数字？想象一下，每个字符都可以用一个数字表示，是这样的吧。
英文:
I have a table that contains a multi-column primary key. For the purpose of ETL job, I need to concatenate all primary key columns into a single column for de-duplication.
Let's say that my combined primary key are:
jesse_20230431_low
james_20230431_high
Is there anyway for me to convert that into a number that retains its uniqueness? Figure every character can be represented by a number no? Something like that.
答案1
得分: 1
你可以使用每个字符的ascii()值，但字符串会变得很大。
查询：
with cte as
(
SELECT ascii(unnest(string_to_array('jesse_20230431_low james_20230431_high', NULL))) col
)
select string_agg(cast(col as varchar), '') from cte
输出：
| string_agg |
|---|
| 10610111511510195504850514852514995108111119321069710910111595504850514852514995104105103104 |
英文:
You can use ascii() value of each character but the string will be huge.
Query:
with cte as
(
SELECT ascii( unnest( string_to_array('jesse_20230431_low james_20230431_high', NULL) ) ) col
)
select string_agg(cast(col as varchar),'') from cte
Output:
| string_agg |
|---|
| 10610111511510195504850514852514995108111119321069710910111595504850514852514995104105103104 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论