CODEWITHSUNDEEP
mysqlarraysstored-procedures
Donny Akhmad Septa Utama
Donny Akhmad Septa Utama

Reputation: 823

MySQL access string like an array

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?

Upvotes: 0

Views: 298

Answers (2)

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

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

Upvotes: 1

GMB
GMB

Reputation: 222462

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                |

Upvotes: 1

Related Questions