Get Random alphanumeric number with specific length using SQL Server

Create a function to get the new guid

create view [dbo].[MyNewGuid] as
select newid() as NewID;

Call below user defined function and pass the number of characters to be returned.

CREATE FUNCTION [dbo].[ufn_RandomString](
    @pStringLength int = 10 -- No of characters to be returned
    ,@isupper bit=0 -- will be returned in upper case or lowercase
) returns varchar(max)
as begin
    declare  @RandomStr varchar(max);
    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),
    Val as (select
                row_number() over (order by N) as N
              from
                a4)
    , cteRandomString (
        RandomString
    ) as (
    select top (@pStringLength)
        substring(x,(abs(checksum((select [NewID] from [MyNewGuid])))%36)+1,1)
    from
        Val cross join (select x='0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') a
    )
     select @RandomStr =
    replace((select
        ',' + RandomString
    from
        cteRandomString
    for xml path ('')),',','');
    if(@isupper=1)
    SET @RandomStr=UPPER(@RandomStr)
    else
    SET @RandomStr=LOWER(@RandomStr)
    return   (@RandomStr);
end

Execute the below query

SELECT dbo.ufn_RandomString(10,0)
SELECT dbo.ufn_RandomString(10,1)

Comments

Popular posts from this blog

List of special Characters in HTML

How to Read TOC Headings from word document using C#