MySQL access string like an array

Question

I'm was trying to make function that will return random character. What I have is a string that contains alphanumerics. In Postgresql I can set text as array, but I don't know how MySQL do same. for example:

in postgresql

DECLARE chars TEXT[] := '{0,1,2,3,4,5,6,7,8,9,A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z}';

So when I want get chars[10] it will return A. How to do this in MySQL?

Slava Rozhnev · Accepted Answer · 2020-10-19 10:59:20Z

1

I think the questoin is not about arrays, it is about get random char, right? In this case You can use next approach in MySQL:

SELECT SUBSTRING(
        '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz', 
        ROUND(RAND()*61)+1,
         1
    ) as random_char;

The SUBSTRING function returns one char from random position.

Try it on SQLize.online

edited Oct 19, 2020 at 10:59

answered Oct 19, 2020 at 10:16

Slava Rozhnev

10.2k6 gold badges27 silver badges43 bronze badges

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

1 Comment

Donny Akhmad Septa Utama Over a year ago

nice and clean. is SUBSTRING more faster than JSON?

GMB · Accepted Answer · 2020-10-19 09:35:17Z

1

Starting MySQL 5.7, one option is to use a JSON array:

set @chars = '["0", "1", "A", "B"]';
select json_unquote(json_extract(@chars, '$[2]')) as value_at_index_2;

Yields:

| value_at_index_2 |
| :--------------- |
| A                |

answered Oct 19, 2020 at 9:35

GMB

224k25 gold badges103 silver badges151 bronze badges

1 Comment

Donny Akhmad Septa Utama Over a year ago

Your answer is nice too. It's more similiar to approach what postgresql does. but I don't want adding double quote each character. If I still use your answer, can we just remove double quote?

Collectives™ on Stack Overflow

MySQL access string like an array

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related