How using SQL to generate random string?

• Garadin

  One Orange Chip

  Points: 29613

  More actions

  November 24, 2009 at 8:46 am

  #1083505

  Using that format, you're lowering your pool to 16 from 36, which only gives you a total 16,777,216 possible permutations and increases your rate of failure over what was originally being tested.

  You still haven't explained why you need to use only 6 characters rather than just sticking with newid() and letting the database enforce uniqueness.

  Seth Phelabaum

  ---

  Consistency is only a virtue if you're not a screwup.

  Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  November 24, 2009 at 4:07 pm

  #1083730

  DECLARE @Codes TABLE(

  Code binary(1) PRIMARY KEY

  )

  INSERT INTO @Codes (Code)

  SELECT CONVERT(binary(1), number)

  FROM master.dbo.spt_values

  WHERE Type = 'P'

  AND (number between 65 and 90 OR CHAR(number) LIKE '[0-9]')

  SELECT

  T1.Code + T2.Code + T3.Code + T4.Code + T5.Code + T6.Code BinaryString,

  CHAR(T1.Code) + CHAR(T2.Code) + CHAR(T3.Code) + CHAR(T4.Code) + CHAR(T5.Code) + CHAR(T6.Code) CharString

  FROM (select TOP 1 Code from @Codes order by NEWID()) T1

  CROSS JOIN (select TOP 1 Code from @Codes order by NEWID()) T2

  CROSS JOIN (select TOP 1 Code from @Codes order by NEWID()) T3

  CROSS JOIN (select TOP 1 Code from @Codes order by NEWID()) T4

  CROSS JOIN (select TOP 1 Code from @Codes order by NEWID()) T5

  CROSS JOIN (select TOP 1 Code from @Codes order by NEWID()) T6

  Table @Codes may be made permanent to avoid populating it every time.

  _____________
  Code for TallyGenerator

• Jeff Moden

  SSC Guru

  Points: 1004617

  More actions

  November 24, 2009 at 4:30 pm

  #1083739

  Garadin (11/24/2009)

  ---

  Using that format, you're lowering your pool to 16 from 36, which only gives you a total 16,777,216 possible permutations and increases your rate of failure over what was originally being tested.

  You still haven't explained why you need to use only 6 characters rather than just sticking with newid() and letting the database enforce uniqueness.

  Heh... probably passwords with some misconception that everyone needs a unique password.

  --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.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)