none
Random Numbers

    질문

  • in sql How To  Generate  Random Numbers and the generated Random Number Should not be repeated give any suggestions

    for this

    2018년 5월 18일 금요일 오전 9:37

답변

  • But you say  you need random numbers? How about

    USE tempdb;
    GO
    DECLARE @s char(6);

    SET @s = (
    SELECT
    c1 AS [text()]
    FROM
    (
    SELECT TOP (6) c1
    FROM
      (
        VALUES
          ('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'), ('0'), ('1'), ('2'), ('3'),
          ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
      ) AS T1(c1)
    ORDER BY ABS(CHECKSUM(NEWID()))
    ) AS T2
    FOR XML PATH('')
    );

    SELECT @s AS [@s];
    GO
     


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence



    2018년 5월 21일 월요일 오전 5:49
    답변자

모든 응답

  • See

    https://www.mssqltips.com/sqlservertip/3055/generating-random-numbers-in-sql-server-without-collisions/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오전 9:51
  • with cte as 
    (
     select   1 as num
     union all
     select cast(num+1 as int)
      from  cte where num<101
    )
    select * from cte


    Saravanan ----------------------------------------------------------------------- Ever tried. Ever failed. No matter. Try Again. Fail again. Fail better.

    2018년 5월 18일 금요일 오전 10:36
  • i don't expect your answer i want to generate  

    C59E02
    D65838
    R6B4E1
    E84FBF
    Q88CB7
    C902BA
    T91A42
    H9C470
    NAC876
    MB0F3B
    GB2A61
    CB63A1
    JC61D2
    KDBCE0
    LE5659
    OE5743
    PEFCCF
    CF275D
    ZF80A1
    EF9CCE
    E4DE9B
    X51997
    Q5490A
    G58F06
    T5A79B
    Y5A971

    2018년 5월 21일 월요일 오전 5:37
  • But you say  you need random numbers? How about

    USE tempdb;
    GO
    DECLARE @s char(6);

    SET @s = (
    SELECT
    c1 AS [text()]
    FROM
    (
    SELECT TOP (6) c1
    FROM
      (
        VALUES
          ('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'), ('0'), ('1'), ('2'), ('3'),
          ('4'), ('5'), ('6'), ('7'), ('8'), ('9')
      ) AS T1(c1)
    ORDER BY ABS(CHECKSUM(NEWID()))
    ) AS T2
    FOR XML PATH('')
    );

    SELECT @s AS [@s];
    GO
     


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence



    2018년 5월 21일 월요일 오전 5:49
    답변자
  • i don't expect your answer i want to generate  

    C59E02
    D65838
    R6B4E1
    E84FBF
    Q88CB7
    C902BA
    T91A42
    H9C470
    NAC876
    MB0F3B
    GB2A61
    CB63A1
    JC61D2
    KDBCE0
    LE5659
    OE5743
    PEFCCF
    CF275D
    ZF80A1
    EF9CCE
    E4DE9B
    X51997
    Q5490A
    G58F06
    T5A79B
    Y5A971

    Hi TamilShyni,

    Or you could try this?

    declare @AlLChars varchar(100) = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
    SELECT RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + 
          RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) +
          RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + 
    	  RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) + 
    	  RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1) +
    	  RIGHT( LEFT(@AlLChars,ABS(BINARY_CHECKSUM(NEWID())%35) + 1 ),1)

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 5월 21일 월요일 오전 6:35
    중재자