Random 64 Characters alphanumeric String

  • AFIFM

    SSC Eights!

    Points: 842

    I need to create a random 64 alphanumeric string every time a new row is added to a SQL 2005 database table. It does not have to be unique.

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    select

    Random_String =

    substring(x,(abs(checksum(newid()))%36)+1,1)+

    substring(x,(abs(checksum(newid()))%36)+1,1)+

    substring(x,(abs(checksum(newid()))%36)+1,1)+

    substring(x,(abs(checksum(newid()))%36)+1,1)+

    /* and so on for as many characters as needed */

    substring(x,(abs(checksum(newid()))%36)+1,1)

    from

    (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a

    Results:

    Random_String

    -------------

    T7TAR

  • Brigadur

    SSCrazy

    Points: 2097

    How about using the builtin newid() funcion like:

    DECLARE @rand AS char(64)

    SELECT @rand=replace(cast(newid() AS varchar(36))+cast(newid() AS varchar(36)),'-','')

    SELECT @rand

  • AFIFM

    SSC Eights!

    Points: 842

    Thank you Istvan, the newid() function works great. The sample code by Michael also does the job nicely except that it will only generates 5 characters string and I need 64 chracters.

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    AFIFM (3/12/2009)


    ...The sample code by Michael also does the job nicely except that it will only generates 5 characters string and I need 64 chracters.

    I just assumed you would be able to figure out the simple changes needed to get 64 characters, since it is only necessary for you to do cut and paste.

    You should be aware that the other solution posted will only return the following characters, and not letters G through Z:

    0123456789ABCDEF

  • jcrawf02

    SSC-Insane

    Points: 24198

    AFIFM (3/11/2009)


    I need to create a random 64 alphanumeric string every time a new row is added to a SQL 2005 database table. It does not have to be unique.

    What are you doing with this random non-unique string?

    ---------------------------------------------------------
    How best to post your question[/url]
    How to post performance problems[/url]
    Tally Table:What it is and how it replaces a loop[/url]

    "stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."

  • Lynn Pettis

    SSC Guru

    Points: 442094

    Here is another solution:

    with

    a1 as (select 1 as N union all

    select 1 union all

    select 1 union all

    select 1 union all

    select 1 union all

    select 1 union all

    select 1 union all

    select 1 union all

    select 1 union all

    select 1),

    a2 as (select

    1 as N

    from

    a1 as a

    cross join a1 as b),

    a3 as (select

    1 as N

    from

    a2 as a

    cross join a2 as b),

    a4 as (select

    1 as N

    from

    a3 as a

    cross join a2 as b),

    Tally as (select

    row_number() over (order by N) as N

    from

    a4)

    , cteRandomString (

    RandomString

    ) as (

    select top (64)

    substring(x,(abs(checksum(newid()))%36)+1,1)

    from

    Tally cross join (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a

    )

    select

    replace((select

    ',' + RandomString

    from

    cteRandomString

    for xml path ('')),',','');

  • GSquared

    SSC Guru

    Points: 260824

    I think I'd be more inclined to use the one that stacks newids together than the one that actually picks more random character strings. Less likely to end up with obscene/offensive strings. Inevitably, this random string will end up being seen by someone who insists on having a problem with the dev who designed it, if it accidentally contains such. Murphy's Law and all that.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • AFIFM

    SSC Eights!

    Points: 842

    jcrawf02 (3/12/2009)


    AFIFM (3/11/2009)


    I need to create a random 64 alphanumeric string every time a new row is added to a SQL 2005 database table. It does not have to be unique.

    What are you doing with this random non-unique string?

    I have an application that takes the user's password and converts it into one-way hash SHA256 64 characters alphanumeric field. We needed to create a second field to use as token (handshake) between two separate applications and I thought I would just use the same thing. If I had to do it from scratch I would have used the newid() function but these are legacy data and it will be cumbersome to change.

  • Lowell

    SSC Guru

    Points: 323331

    ahh Lynn I like that code;

    I parameterized the TOP (64) with @top, and can use that to generate any length whether 64 here, ro whatever i might need.

    very nice!

    Thanks for the addition to my snippets collection!

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Matt Miller (4)

    SSC Guru

    Points: 124150

    declare @n varchar(64)

    set @n='';

    ;with MyCTE1 as (

    select N, newid() as rid from tally),

    MyCte2 as (

    select top(64) char(n%84+32) randchar from MyCTE1 order by rid)

    select @n=@n+randchar

    from MyCTE2

    select @n

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Michael Valentine Jones

    SSC Guru

    Points: 64818

    Another variation from Lynn's version:

    ;with

    a1 as (

    select 1 as N union all select 1 union all select 1 union all select 1 union all

    select 1 union all select 1 union all select 1 union all select 1

    ),

    cteRandomString as (

    select

    substring('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',(abs(checksum(newid()))%36)+1,1)

    as RandomString

    from

    ( select 1 as N from a1 as a cross join a1 as b ) a

    )

    select

    convert(varchar(64),replace((

    select

    ','+RandomString

    from

    cteRandomString

    for xml path (''))

    ,',','')) as RandomString;

  • Lowell

    SSC Guru

    Points: 323331

    great code examples; what if i wanted to generate X number strings, for arguments sake lets say 10...i can't seem to visualize how to generate multiple records though.

    rub my nose in it and get the newspaper....

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Matt Miller (4)

    SSC Guru

    Points: 124150

    here's one way. Not necessarily the most efficient, but it's not bad.

    drop table #matt

    declare @rows_needed int

    declare @length_needed int

    select @rows_needed=1000,

    @length_needed=64

    select top(@rows_needed*@length_needed)

    identity(int,1,1) RN

    ,cast(N as int) N

    ,0 as batchcol

    ,'' as randchar

    into #matt

    from tally

    order by newid()

    update #matt

    set batchcol = cast(rn/@length_needed as int),

    randchar = char(n%52+65)

    create index m on #matt(batchcol) include(randchar)

    select distinct batchcol,

    (select

    randchar as 'text()'

    from

    #matt m_in

    where m_in.batchcol=m_out.batchcol

    for xml path ('')) from #matt m_out

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Jeff Moden

    SSC Guru

    Points: 993658

    I know this is a really old thread but, to avoid building a random foul word generator, there's an easy method to do this.

    SELECT REPLACE(CONVERT(CHAR(36),NEWID())+CONVERT(CHAR(36),NEWID()),'-','')

    p.s. This does sound like an interview question.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply