Generating random strings with T-SQL

Question

If you wanted to generate a pseudorandom alphanumeric string using T-SQL  how would you do it   How would you exclude characters like dollar signs  dashes  and slashes from it

User · Answer

This uses rand with a seed like one of the other answers, but it is not necessary to provide a seed on every call. Providing it on the first call is sufficient.

This is my modified code.

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND object_id = OBJECT_ID(N'usp_generateIdentifier'))
DROP PROCEDURE usp_generateIdentifier
GO

create procedure usp_generateIdentifier
    @minLen int = 1
    , @maxLen int = 256
    , @seed int output
    , @string varchar(8000) output
as
begin
    set nocount on;
    declare @length int;
    declare @alpha varchar(8000)
        , @digit varchar(8000)
        , @specials varchar(8000)
        , @first varchar(8000)

    select @alpha = 'qwertyuiopasdfghjklzxcvbnm'
        , @digit = '1234567890'
        , @specials = '_@#$&'
    select @first = @alpha + '_@';

    -- Establish our rand seed and store a new seed for next time
    set  @seed = (rand(@seed)*2147483647);

    select @length = @minLen + rand() * (@maxLen-@minLen);
    --print @length

    declare @dice int;
    select @dice = rand() * len(@first);
    select @string = substring(@first, @dice, 1);

    while 0 < @length 
    begin
        select @dice = rand() * 100;
        if (@dice < 10) -- 10% special chars
        begin
            select @dice = rand() * len(@specials)+1;
            select @string = @string + substring(@specials, @dice, 1);
        end
        else if (@dice < 10+10) -- 10% digits
        begin
            select @dice = rand() * len(@digit)+1;
            select @string = @string + substring(@digit, @dice, 1);
        end
        else -- rest 80% alpha
        begin
            select @dice = rand() * len(@alpha)+1;

            select @string = @string + substring(@alpha, @dice, 1);
        end

        select @length = @length - 1;   
    end
end
go

User · Answer

This will produce a string 96 characters in length  from the Base64 range  uppers  lowers  numbers    and      Adding 3  NEWID    will increase the length by 32  with no Base64 padding           SELECT          CAST              CONVERT NVARCHAR MAX                   CONVERT VARBINARY 8   NEWID                     CONVERT VARBINARY 8   NEWID                     CONVERT VARBINARY 8   NEWID                     CONVERT VARBINARY 8   NEWID                     CONVERT VARBINARY 8   NEWID                     CONVERT VARBINARY 8   NEWID                     CONVERT VARBINARY 8   NEWID                     CONVERT VARBINARY 8   NEWID                     CONVERT VARBINARY 8   NEWID                 2           AS XML  value  xs base64Binary xs hexBinary        VARCHAR MAX    AS StringValue   If you are applying this to a set  make sure to introduce something from that set so that the NEWID   is recomputed  otherwise you ll get the same value each time     SELECT      U UserName       LEFT PseudoRandom StringValue  LEN U Pwd   AS FauxPwd   FROM Users U     CROSS APPLY           SELECT              CAST                  CONVERT NVARCHAR MAX                       CONVERT VARBINARY 8   NEWID                         CONVERT VARBINARY 8   NEWID                         CONVERT VARBINARY 8   NEWID                         CONVERT VARBINARY 8   NEWID                         CONVERT VARBINARY 8   NEWID                         CONVERT VARBINARY 8   NEWID                         CONVERT VARBINARY 8   NEWID                         CONVERT VARBINARY 8   NEWID                         CONVERT VARBINARY 8   NEWID                         CONVERT VARBINARY 8   NEWID                         CONVERT VARBINARY 8   U UserID   -- Causes a recomute of all NEWID   calls                  2               AS XML  value  xs base64Binary xs hexBinary        VARCHAR MAX    AS StringValue       PseudoRandom

User · Answer

Here s one I came up with today  because I didn t like any of the existing answers enough    This one generates a temp table of random strings  is based off of newid    but also supports a custom character set  so more than just 0-9  amp  A-F   custom length  up to 255  limit is hard-coded  but can be changed   and a custom number of random records   Here s the source code  hopefully the comments help           First  we re going to define the random parameters for this    snippet  Changing these variables will alter the entire    outcome of this script  Try not to break everything         var  int        count    The number of random values to generate      var  int        length   The length of each random value      var  char 62    charset  The characters that may appear within a random value       -- Define the parameters declare  count int   10 declare  length int   60 declare  charset char 62     ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789          We re going to define our random table to be twice the maximum    length  255   2   510   It s twice because we will be using    the newid   method  which produces hex guids  More later       -- Create the random table declare  random table       value nvarchar 510            We ll use two characters from newid   to make one character in    the random value  Each newid   provides us 32 hex characters     so we ll have to make multiple calls depending on length       -- Determine how many  newid    calls we ll need per random value declare  iterations int   ceiling  length   2   32 0          Before we start making multiple calls to  newid   we need to    start with an initial value  Since we know that we need at    least one call  we will go ahead and satisfy the count       -- Iterate up to the count declare  i int   0 while  i  lt   count begin set  i    i   1      -- Insert a new set of 32 hex characters for each record  limiting to  length   2     insert into  random         select substring replace newid     -        1   length   2   end  -- Now fill the remaining the remaining length using a series of update clauses set  i   0 while  i  lt   iterations begin set  i    i   1      -- Append to the original value  limit  length   2     update  random         set value   substring value   replace newid     -        1   length   2   end         Now that we have our base random values  we can convert them    into the final random values  We ll do this by taking two    hex characters  and mapping then to one charset value       -- Convert the base random values to charset random values set  i   0 while  i  lt   length begin set  i    i   1                 Explaining what s actually going on here is a bit complex  I ll        do my best to break it down step by step  Hopefully you ll be        able to follow along  If not  then wise up and come back               -- Perform the update     update  random         set value                                   Everything we re doing here is in a loop  The  i variable marks                what character of the final result we re assigning  We will                start off by taking everything we ve already done first                               -- Take the part of the string up to the current index             substring value  1   i - 1                                    Now we re going to convert the two hex values after the index                 and convert them to a single charset value  We can do this                with a bit of math and conversions  so function away                               -- Replace the current two hex values with one charset value             substring  charset  convert int  convert varbinary 1   substring value   i  2   2      len  charset  - 1    255   1  1        --   1  -------------------------------------------------------                       -----------------------------------------     --   2  ---------------------------------                      11111111111111111111111    -------------------------------------     --   3  --------------------             2222222222222222222222222222222222222222222222222 ------------------------------------     --   4  --------------------333333333333333333333333333333333333333333333333333333333333333---                         --------     --   5  --------------------333333333333333333333333333333333333333333333333333333333333333   4444444444444444444444444--------     --   6  --------------------5555555555555555555555555555555555555555555555555555555555555555555555555555555555555555555    ----     --   7                      66666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666666                                      1  - Determine the two hex characters that we ll be converting  ex  0F  AB  3C  etc                   2  - Convert those two hex characters to a a proper hexadecimal  ex  0x0F  0xAB  0x3C  etc                   3  - Convert the hexadecimals to integers  ex  15  171  60                  4  - Determine the conversion ratio between the length of  charset and the range of hexadecimals  255                  5  - Multiply the integer from  3  with the conversion ratio from  4  to get a value between 0 and  len  charset  - 1                  6  - Add 1 to the offset from  5  to get a value between 1 and len  charset   since strings start at 1 in SQL                 7  - Use the offset from  6  and grab a single character from  subset                                                 All that is left is to add in everything we have left to do                 We will eventually process the entire string  but we will                take things one step at a time  Round and round we go                               -- Append everything we have left to do             substring value  2    i  len value    end  -- Select the results select value from  random   It s not a stored procedure  but it wouldn t be that hard to turn it into one  It s also not horrendously slow  it took me  0 3 seconds to generate 1 000 results of length 60  which is more than I ll ever personally need   which was one of my initial concerns from all of the string mutation I m doing   The main takeaway here is that I m not trying to create my own random number generator  and my character set isn t limited  I m simply using the random generator that SQL has  I know there s rand    but that s not great for table results   Hopefully this approach marries the two kinds of answers here  from overly simple  i e  just newid    and overly complex  i e  custom random number algorithm    It s also short  minus the comments   and easy to understand  at least for me   which is always a plus in my book   However  this method cannot be seeded  so it s going to be truly random each time  and you won t be able to replicate the same set of data with any means of reliability  The OP didn t list that as a requirement  but I know that some people look for that sort of thing   I know I m late to the party here  but hopefully someone will find this useful

User · Answer

Based on various helpful responses in this article I landed with a combination of a couple options I liked   DECLARE  UserId BIGINT   12345 -- a uniqueId in my system SELECT LOWER REPLACE NEWID    -         CONVERT VARCHAR   UserId

User · Answer

I did this in SQL 2000 by creating a table that had characters I wanted to use  creating a view that selects characters from that table ordering by newid    and then selecting the top 1 character from that view   CREATE VIEW dbo vwCodeCharRandom AS SELECT TOP 100 PERCENT      CodeChar FROM dbo tblCharacter ORDER BY      NEWID         SELECT TOP 1 CodeChar FROM dbo vwCodeCharRandom   Then you can simply pull characters from the view and concatenate them as needed   EDIT  Inspired by Stephan s response     select top 1 RandomChar from tblRandomCharacters order by newid     No need for a view  in fact I m not sure why I did that - the code s from several years back    You can still specify the characters you want to use in the table

User · Answer

There are a lot of good answers but so far none of them allow a customizable character pool and work as a default value for a column  I wanted to be able to do something like this   alter table MY TABLE add MY COLUMN char 20  not null   default dbo GenerateToken crypt gen random 20     So I came up with this  Beware of the hard-coded number 32 if you modify it   -- Converts a varbinary of length N into a varchar of length N  -- Recommend passing in the result of CRYPT GEN RANDOM N   create function GenerateToken  randomBytes varbinary max   returns varchar max  as begin  -- Limit to 32 chars to get an even distribution  because 32 divides 256  with easy math  declare  allowedChars char 32   set  allowedChars    abcdefghijklmnopqrstuvwxyz012345    declare  oneByte tinyint  declare  oneChar char 1   declare  index int  declare  token varchar max    set  index   0  set  token        while  index  lt  datalength  randomBytes  begin     -- Get next byte  use it to index into  allowedChars  and append to  token      -- Note  substring is 1-based      set  index    index   1      select  oneByte   convert tinyint  substring  randomBytes   index  1        select  oneChar   substring  allowedChars  1     oneByte   32   1   -- 32 is the number of  allowedChars     select  token    token    oneChar  end  return  token   end

User · Answer

Use the following code to return a short string   SELECT SUBSTRING CONVERT varchar 40   NEWID    0 9

User · Answer

Heres something based on New Id    with list as        select 1 as id newid   as val          union all     select id   1 NEWID       from    list        where   id   1  lt  10    select ID val from list option  maxrecursion 0

User · Answer

I came across this blog post first  then came up with the following stored procedure for this that I m using on a current project  sorry for the weird formatting    CREATE PROCEDURE  dbo   SpGenerateRandomString   sLength tinyint   10   randomString varchar 50  OUTPUT AS BEGIN SET NOCOUNT ON DECLARE  counter tinyint DECLARE  nextChar char 1  SET  counter   1 SET  randomString        WHILE  counter  lt    sLength BEGIN SELECT  nextChar   CHAR 48   CONVERT INT   122-48 1  RAND      IF ASCII  nextChar  not in  58 59 60 61 62 63 64 91 92 93 94 95 96  BEGIN SELECT  randomString    randomString    nextChar SET  counter    counter   1 END END END

User · Answer

I m not expert in T-SQL  but the simpliest way I ve already used it s like that   select char  rand   25   65   char  rand   25   65     This generates two char  A-Z  in ascii 65-90

User · Answer

In SQL Server 2012  we could concatenate the binaries of some  G UIDs and then do a base64 conversion on the result   SELECT      textLen textLen     left           select  CAST newid   as varbinary max     CAST newid   as varbinary max            where   textLen textLen is not null   force evaluation for each outer query row            FOR XML PATH      BINARY BASE64       textLen textLen    as  randomText FROM   values  2   4   48    as textLen textLen     --define lengths here     If you need longer strings  or you see   characters in the result  you need to add more   CAST newid   as varbinary max   in the sub select

User · Answer

When generating random data  specially for test  it is very useful to make the data random  but reproducible  The secret is to use explicit seeds for the random function  so that when the test is run again with the same seed  it produces again exactly the same strings  Here is a simplified example of a function that generates object names in a reproducible manner   alter procedure usp generateIdentifier      minLen int   1        maxLen int   256        seed int output        string varchar 8000  output as begin     set nocount on      declare  length int      declare  alpha varchar 8000             digit varchar 8000             specials varchar 8000             first varchar 8000      declare  step bigint   rand  seed    2147483647       select  alpha    qwertyuiopasdfghjklzxcvbnm             digit    1234567890             specials              select  first    alpha              set   seed    rand   seed  step  2147483647  2147483647        select  length    minLen   rand  seed      maxLen- minLen             seed    rand   seed  step  2147483647  2147483647        declare  dice int      select  dice   rand  seed    len  first            seed    rand   seed  step  2147483647  2147483647       select  string   substring  first   dice  1        while 0  lt   length      begin         select  dice   rand  seed    100                seed    rand   seed  step  2147483647  2147483647           if   dice  lt  10  -- 10  special chars         begin             select  dice   rand  seed    len  specials  1                    seed    rand   seed  step  2147483647  2147483647               select  string    string   substring  specials   dice  1           end         else if   dice  lt  10 10  -- 10  digits         begin             select  dice   rand  seed    len  digit  1                    seed    rand   seed  step  2147483647  2147483647               select  string    string   substring  digit   dice  1           end         else -- rest 80  alpha         begin             declare  preseed int    seed              select  dice   rand  seed    len  alpha  1                    seed    rand   seed  step  2147483647  2147483647                select  string    string   substring  alpha   dice  1           end          select  length    length - 1         end end go   When running the tests the caller generates a random seed it associates with the test run  saves it in the results table   then passed along the seed  similar to this   declare  seed int  declare  string varchar 256    select  seed   1234  -- saved start seed  exec usp generateIdentifier       seed    seed output        string    string output  print  string    exec usp generateIdentifier       seed    seed output        string    string output  print  string    exec usp generateIdentifier       seed    seed output        string    string output  print  string      Update 2016-02-17  See the comments bellow  the original procedure had an issue in the way it advanced the random seed  I updated the code  and also fixed the mentioned off-by-one issue

User · Answer

So I liked a lot of the answers above  but I was looking for something that was a little more random in nature   I also wanted a way to explicitly call out excluded characters   Below is my solution using a view that calls the CRYPT GEN RANDOM to get a cryptographic random number   In my example  I only chose a random number that was 8 bytes   Please note  you can increase this size and also utilize the seed parameter of the function if you want   Here is the link to the documentation  https   docs microsoft com en-us sql t-sql functions crypt-gen-random-transact-sql  CREATE VIEW  dbo   VW CRYPT GEN RANDOM 8  AS SELECT CRYPT GEN RANDOM 8  as  value     The reason for creating the view is because CRYPT GEN RANDOM cannot be called directly from a function    From there  I created a scalar function that accepts a length and a string parameter that can contain a comma delimited string of excluded characters   CREATE FUNCTION  dbo   fn GenerateRandomString          length INT       excludedCharacters VARCHAR 200  --Comma delimited string of excluded characters   RETURNS VARCHAR Max  BEGIN     DECLARE  returnValue VARCHAR Max                  asciiValue INT            currentCharacter CHAR       --Optional concept  you can add default excluded characters     SET  excludedCharacters   CONCAT  excludedCharacters           -                             lt     gt                --Table of excluded characters     DECLARE  excludedCharactersTable table  asciiValue  INT        --Insert comma     INSERT INTO  excludedCharactersTable SELECT 44       --Stores the ascii value of the excluded characters in the table     INSERT INTO  excludedCharactersTable     SELECT ASCII TRIM value       FROM STRING SPLIT  excludedCharacters           WHERE LEN TRIM value     1       --Keep looping until the return string is filled     WHILE LEN  returnValue   lt   length      BEGIN         --Get a truly random integer values from 33-126         SET  asciiValue    SELECT TOP 1  ABS CONVERT INT   value      94    33 FROM  dbo   VW CRYPT GEN RANDOM 8             --If the random integer value is not in the excluded characters table then append to the return string         IF NOT EXISTS SELECT                            FROM  excludedCharactersTable                          WHERE  asciiValue     asciiValue           BEGIN             SET  returnValue    returnValue   CHAR  asciiValue           END     END      RETURN  returnValue   END   Below is an example of the how to call the function   SELECT  dbo   fn GenerateRandomString  8             amp          Cheers

User · Answer

If you are running SQL Server 2008 or greater  you could use the new cryptographic function crypt gen random   and then use base64 encoding to make it a string   This will work for up to 8000 characters   declare  BinaryData varbinary max         CharacterData varchar max         Length int   2048  set  BinaryData crypt gen random   Length    set  CharacterData cast    as xml  value  xs base64Binary sql variable   BinaryData       varchar max     print  CharacterData

User · Answer

I thought I d share  or give back to the community      It s ASCII based  and the solution is not perfect but it works quite well  Enjoy  Goran B       -- predictable masking of ascii chars within a given decimal range -- purpose   --    i needed an alternative to hashing alg  or uniqueidentifier functions --    because i wanted to be able to revert to original char set if possible   if   the operative word  -- notes  wrap below in a scalar function if desired  i e  recommended  -- by goran biljetina  2014-02-25      declare   length int   position int   maskedString varchar 500    inpString varchar 500    offsetAsciiUp1 smallint   offsetAsciiDown1 smallint   ipOffset smallint   asciiHiBound smallint   asciiLoBound smallint   set  ipOffset null set  offsetAsciiUp1 1 set  offsetAsciiDown1 -1 set  asciiHiBound 126 -- gt  up to and NOT including set  asciiLoBound 31 -- gt  up from and NOT including  SET  inpString      config   some string value    boolAttr   true   SET  length   LEN  inpString   SET  position   1 SET  maskedString       -- gt  MASK  --------- WHILE   position  lt   length 1  BEGIN     SELECT  maskedString    maskedString        ISNULL          CASE          WHEN ASCII SUBSTRING  inpString  position 1   gt  asciiLoBound AND ASCII SUBSTRING  inpString  position 1   lt  asciiHiBound          THEN             CHAR ASCII SUBSTRING  inpString  position 1                 case when  ipOffset is null then             case when ASCII SUBSTRING  inpString  position 1   2 0 then  offsetAsciiUp1 else  offsetAsciiDown1 end             else  ipOffset end           WHEN ASCII SUBSTRING  inpString  position 1   lt   asciiLoBound          THEN     CONVERT varchar ASCII SUBSTRING  Inpstring  position 1   1000      -- gt  wrap for decode         WHEN ASCII SUBSTRING  inpString  position 1   gt   asciiHiBound          THEN     CONVERT varchar ASCII SUBSTRING  inpString  position 1   1000      -- gt  wrap for decode         END                  SELECT  position    position   1 END  select  MaskedString   SET  inpString    maskedString SET  length   LEN  inpString   SET  position   1 SET  maskedString       -- gt  UNMASK  Limited to within ascii lo-hi bound   ------------------------------------------------- WHILE   position  lt   length 1  BEGIN     SELECT  maskedString    maskedString        ISNULL          CASE          WHEN ASCII SUBSTRING  inpString  position 1   gt  asciiLoBound AND ASCII SUBSTRING  inpString  position 1   lt  asciiHiBound          THEN             CHAR ASCII SUBSTRING  inpString  position 1                 case when  ipOffset is null then             case when ASCII SUBSTRING  inpString  position 1   2 1 then  offsetAsciiDown1 else  offsetAsciiUp1 end             else  ipOffset  -1  end           ELSE            END                  SELECT  position    position   1 END  select  maskedString

User · Answer

Sometimes we need a lot of random things  love  kindness  vacation  etc   I have collected a few random generators over the years  and these are from Pinal Dave and a stackoverflow answer I found once  Refs below   --Adapted from Pinal Dave  http   blog sqlauthority com 2007 04 29 sql-server-random-number-generator-script-sql-query  SELECT      ABS  CAST  NEWID   AS BINARY  6    1000    1 AS RandomInt       CAST   ABS  CAST  NEWID   AS BINARY  6    1000    1  7 0123 AS NUMERIC  15 4   AS RandomNumeric       DATEADD  DAY  -1  ABS  CAST  NEWID   AS BINARY  6    1000    1   GETDATE    AS RandomDate     --This line from http   stackoverflow com questions 15038311 sql-password-generator-8-characters-upper-and-lower-and-include-a-number       CAST  ABS CHECKSUM NEWID     10  AS VARCHAR 1     CHAR ASCII  a    ABS CHECKSUM NEWID     25     CHAR ASCII  A    ABS CHECKSUM NEWID     25     LEFT NEWID   5  AS RandomChar       ABS CHECKSUM NEWID     50000 1 AS RandomID

User · Answer

Similar to the first example  but with more flexibility  -- min length   8  max length   12 SET  Length   RAND     5   8 -- SET  Length   RAND      max length - min length   1    min length  -- define allowable character explicitly - easy to read this way an easy to  -- omit easily confused chars like l  ell  and 1  one  or 0  zero  and O  oh  SET  CharPool         abcdefghijkmnopqrstuvwxyzABCDEFGHIJKLMNPQRSTUVWXYZ23456789  -        amp    SET  PoolLength   Len  CharPool   SET  LoopCount   0 SET  RandomString       WHILE   LoopCount  lt   Length  BEGIN     SELECT  RandomString    RandomString            SUBSTRING  Charpool  CONVERT int  RAND      PoolLength    1  1      SELECT  LoopCount    LoopCount   1 END  I forgot to mention one of the other features that makes this more flexible   By repeating blocks of characters in  CharPool  you can increase the weighting on certain characters so that they are more likely to be chosen

User · Answer

I realize that this is an old question with many fine answers  However when I found this I also found a more recent article on TechNet by Saeid Hasani  T-SQL  How to Generate Random Passwords  While the solution focuses on passwords it applies to the general case  Saeid works through various considerations to arrive at a solution  It is very instructive   A script containing all the code blocks form the article is separately available via the TechNet Gallery  but I would definitely start at the article

User · Answer

it s very simply use it and enjoy   CREATE VIEW  dbo   vwGetNewId  AS SELECT        NEWID   AS Id  Creat FUNCTION  dbo   fnGenerateRandomString   length INT   8  RETURNS NVARCHAR MAX  AS BEGIN  DECLARE  result CHAR 2000    DECLARE  String VARCHAR 2000    SET  String    abcdefghijklmnopqrstuvwxyz    --lower letters  ABCDEFGHIJKLMNOPQRSTUVWXYZ    --upper letters  1234567890   --number characters  SELECT  result         SELECT TOP   length             SUBSTRING  String  1   number  1  AS  text        FROM master  spt values     WHERE number  lt  DATALENGTH  String            AND type    P      ORDER BY       SELECT TOP 1 Id FROM dbo vwGetNewId     --instead of using newid       FOR XML PATH         RETURN  result   END

User · Answer

This worked for me  I needed to generate just three random alphanumeric characters for an ID  but it could work for any length up to 15 or so    declare  DesiredLength as int   3  select substring replace newID    -      cast RAND    31- DesiredLength  as int   DesiredLength

User · Answer

Below are the way to Generate 4 Or 8 Characters Long Random Alphanumeric String in SQL select LEFT CONVERT VARCHAR 36  NEWID    4  RIGHT CONVERT VARCHAR 36  NEWID    4     SELECT RIGHT REPLACE CONVERT VARCHAR 36  NEWID     -      8

User · Answer

Here is a random alpha numeric generator  print left replace newid    -       length    -- length is the length of random Num

User · Answer

For SQL Server 2016 and later  here is a really simple and relatively efficient expression to generate cryptographically random strings of a given byte length   --Generates 36 bytes  48 characters  of base64 encoded random data select r from OpenJson  select Crypt Gen Random 36  r for json path      with  r varchar max     Note that the byte length is not the same as the encoded size  use the following from this article to convert   Bytes   3    LengthInCharacters   4  - Padding

User · Answer

For one random letter  you can use   select substring  ABCDEFGHIJKLMNOPQRSTUVWXYZ                     abs checksum newid       26  1  1    An important difference between using newid   versus rand   is that if you return multiple rows  newid   is calculated separately for each row  while rand   is calculated once for the whole query

User · Answer

I use this procedure that I developed simply stipluate the charaters you want to be able to display in the input variables  you can define the length too  Hope this formats well  I am new to stack overflow   IF EXISTS  SELECT   FROM sys objects WHERE type    P  AND object id   OBJECT ID N GenerateARandomString    DROP PROCEDURE GenerateARandomString GO  CREATE PROCEDURE GenerateARandomString         DESIREDLENGTH         INTEGER   100                          NUMBERS               VARCHAR 50              0123456789              ALPHABET              VARCHAR 100             ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz         SPECIALS              VARCHAR 50                 -      amp                    RANDOMSTRING          VARCHAR 8000    OUT      AS  BEGIN     -- Author David Riley     -- Version 1 0      -- You could alter to one big string  e e numebrs   alpha special etc     -- added for more felxibility in case I want to extend i e put logic  in for 3 numbers  2 pecials 3 numbers etc     -- for now just randomly pick one of them      DECLARE  SWAP                   VARCHAR 8000        -- Will be used as a tempoary buffer      DECLARE  SELECTOR               INTEGER   0       DECLARE  CURRENTLENGHT          INTEGER   0      WHILE  CURRENTLENGHT  lt   DESIREDLENGTH     BEGIN          -- Do we want a number  special character or Alphabet Randonly decide          SET  SELECTOR    CAST ABS CHECKSUM NEWID       3 AS INTEGER     -- Always three 1 number   2 alphaBET   3 special          IF  SELECTOR   0         BEGIN             SET  SELECTOR   3         END           -- SET SWAP VARIABLE AS DESIRED         SELECT  SWAP   CASE WHEN  SELECTOR   1 THEN  NUMBERS WHEN  SELECTOR   2 THEN  ALPHABET ELSE  SPECIALS END           -- MAKE THE SELECTION         SET  SELECTOR    CAST ABS CHECKSUM NEWID       LEN  SWAP  AS INTEGER           IF  SELECTOR   0         BEGIN             SET  SELECTOR   LEN  SWAP          END           SET  RANDOMSTRING   ISNULL  RANDOMSTRING       SUBSTRING  SWAP  SELECTOR 1           SET  CURRENTLENGHT   LEN  RANDOMSTRING       END   END   GO  DECLARE  RANDOMSTRING VARCHAR 8000   EXEC GenerateARandomString  RANDOMSTRING    RANDOMSTRING OUT  SELECT  RANDOMSTRING

User · Answer

Using a guid  SELECT  randomString   CONVERT varchar 255   NEWID      very short

User · Answer

select left NEWID   5    This will return the 5 left most characters of the guid string  Example run ------------ 11C89 9DB02

[sql] Generating random strings with T-SQL

Examples related to sql

Examples related to tsql

Examples related to random