SQL server random numbers

Question

In this code only returns a value, I need a list of values, as I do?

 declare @letras varchar(max) = 'ABCDEFGHIJKLMNOPQRSTUWVXZ1234567890'
    declare @tamanho int = 10
    ;with cte as (
        select
            1 as contador,
            substring(@letras, 1 + (abs(checksum(newid())) % len(@letras)), 1) as letra
        union all
        select
            contador + 1,
            substring(@letras, 1 + (abs(checksum(newid())) % len(@letras)), 1)
        from cte where contador < @tamanho)
    --select * from cte option (maxrecursion 0)
    select (
        select '' + letra from cte
        for xml path(''), type, root('txt')
        ).value ('/txt[1]', 'varchar(max)')
    option (maxrecursion 0)

Answer 1

Well, its need is to return a list of random values, for this I adapted creating a temporary table defining a loop for fed line:

You can test on any bench..

DECLARE @Retorno char(100)

DECLARE @total int = 1000
        ,@linha INT = 1

DECLARE @table TABLE
(
    Id INT IDENTITY(1,1),
    Valor VARCHAR(MAX)
)

WHILE (@linha < @total)
BEGIN
SET @Retorno = (
    SELECT 
        c1 AS [text()]
    FROM(
        SELECT TOP (1000) 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('')
)

    INSERT INTO @table VALUES (LTRIM(RTRIM(@Retorno)))

    SET @linha = @linha + 1
END


SELECT Valor FROM @table

Note: If you want to increase the number of loops, just increase the value of the variable @total.

I hope I’ve helped.

Answer 2

Lucas Lira, I incremented as requested to filter the amount of characters to return, return only numbers or only letters or all.

Create this function:

     CREATE FUNCTION [dbo].[fnRemoveCaracteresNaoNumericos](@Temp varchar(1000))
         RETURNS varchar(1000)

         AS

        BEGIN
        --Defini escala dos numeros
        DECLARE @Manter AS varchar(50) = '%[^0-9]%'

        WHILE PATINDEX(@Manter, @Temp) > 0

          SET @Temp = STUFF(@Temp, PATINDEX(@Manter, @Temp), 1, '')

        RETURN @Temp

      END

Now use the updated script:

    DECLARE @Retorno CHAR(100)
    -- Digitar tipo de retorno -> 'texto' / 
    -- 'numeros' / 'todos'
    DECLARE @type VARCHAR(10) = 'texto' 
    DECLARE @total int = 1000
    ,@linha INT = 1
    DECLARE @QtdeCaracter INT = 3 -- Defini qtde de caracters que ira retornar

    DECLARE @table TABLE
    (
      Id INT IDENTITY(1,1), 
      Valor VARCHAR(MAX)
    )

   WHILE (@linha < @total)
   BEGIN
      SET @Retorno = (
                      SELECT 
                      c1 AS [text()]
                      FROM(
                          SELECT TOP (1000) 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('')
       )

       INSERT INTO @table 
       VALUES 
       (LTRIM
       (RTRIM(
            (SELECT 
                  CASE 
                    WHEN (@type = 'Numeros') 
                        THEN dbo.fnRemoveCaracteresNaoNumericos(@Retorno) 
                    WHEN (@type = 'texto') 
                        THEN  
             REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                              REPLACE(@Retorno, 0, ''), 1, ''), 2, ''), 3, ''), 4, 
             ''), 5, ''), 6, ''), 7, ''), 8, ''), 9, '')
                          ELSE @Retorno
                      END))))

                       SET @linha = @linha + 1
                       END

            --Distinct para nao repetir os valores, com funcão rtrim e ltrim para 
            -- limpar espacos em branco
           SELECT DISTINCT RTRIM(LTRIM(SUBSTRING(valor, 1, @QtdeCaracter))) [Valor]
           FROM @table
           ORDER BY Valor