Postgres随机播放随机字符 [英] postgres shuffle random character
本文介绍了Postgres随机播放随机字符的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想改组(ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890)并从1到6获得子字符串。
I want to shuffle (ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890) and get substring from 1 to 6.
我已经尝试了很多,但是在postgres中找不到改组功能
I have tried a lot but was not able to find a shuffle function in postgres
update tablename
set secureno=substring(shuffle(ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890) from 1 for 6)
推荐答案
您可以使用以下查询对字符串进行混洗:
You can shuffle a string using this query:
select substr('ABCD', i, 1)
from generate_series(1, 4) i
order by random();
substr
--------
B
C
A
D
(4 rows)
使用它作为密钥(62个字符),获取6个字符并汇总为字符串:
Use it for your key (with 62 chars), get 6 chars and aggregate to string:
select string_agg(ch, '')
from (
select substr('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890', i, 1) ch
from generate_series(1, 62) i
order by random()
limit 6
) s
如果经常需要,您可以基于算法创建一个函数:
You can create a function based on the algorithm if you often need this::
create or replace function shuffle(text)
returns text language sql as $$
select string_agg(ch, '')
from (
select substr($1, i, 1) ch
from generate_series(1, length($1)) i
order by random()
) s
$$;
select left(shuffle('ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz1234567890'), 6);
这篇关于Postgres随机播放随机字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文