CODEWITHSUNDEEP
sqldatabasedb2
Chris Quibell
Chris Quibell

Reputation: 339

SQL- Creating a random varchar with length of 5

I need to create a varchar(5) value using SQL. The string can only have letters and numbers, no special characters. I have tried this (see below) but this did not work.

CHAR(ROUND(RAND() * 93 + 30, 0));

any idea of what is going on would be appreciated. The full code is listed below.

merge into fundtype as fund using (select fundtypnum, fundtype, country from table(values 
('JRLH5','$FOCA$01$$','USA'),
('YLHOV','$FOCA$02$$','AUS'),
('0MGJA','$FOCA$02$$','USA'),
('UK235','$FOCA$03$$','CAN'),
('HJP3C','$FOCA$03$$','USA')
))
as newfund (fundtypnum,fund_type,country) 
on (
 (fund.fund_type = newfund.fund_type and fund.country = newfund.country) or
 (fund.fundtypnum = newfund.fundtypnum)
)
when not matched then 
 insert (fundtypnum,fund_type,country) values (
    CHAR(ROUND(RAND() * 93 + 30, 0)),
    newfund.fund_type,
 newfund.country);

Upvotes: 0

Views: 180

Answers (2)

BWS
BWS

Reputation: 3846

Expanding on Dan Bracuk's comment ... try something like this:

SELECT substring(reverse(sys.fn_varbintohexstr(CONVERT(varBINARY(8),
ABS(Checksum(NewID()) % 1000000)))),1,5)

Upvotes: 0

Kep Brown
Kep Brown

Reputation: 11

Change CHAR(ROUND(RAND() * 93 + 30, 0)) to

CONCAT(substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890',CEIL(RAND()*36),1),
    substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890',CEIL(RAND()*36),1),
    substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890',CEIL(RAND()*36),1),
    substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890',CEIL(RAND()*36),1),
    substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890',CEIL(RAND()*36),1))

Upvotes: 1

Related Questions