Jun 23, 2015

Generate minimum length unique character of given number SQL server

Sometimes there is a need to convert the numeric digits to unique alpha numeric character in minimum possible length in SQL SERVER.  By using below functions we can achieve this goal. 

Numeric to Alphanumeric (Case sensitive) 

CREATE FUNCTION dbo.GetUniqueCharAlaphNum(@Num AS BIGINT)
RETURNS VARCHAR(100)
BEGIN

      DECLARE @R AS INT
      DECLARE @UniqueString AS VARCHAR(100) = ''
     
      WHILE @Num <> 0 BEGIN
            SET @R = @Num % 62
            SET @Num = @Num / 62
           
            SET @UniqueString = @UniqueString + CHAR(CASE
                  WHEN @R BETWEEN 0 AND 9 
                  THEN @R + 48 
                  WHEN @R BETWEEN 10 AND 35
                  THEN @R + 55
                  WHEN @R BETWEEN 36 AND 61 
                  THEN @R + 61
            END)
      END
     
      RETURN @UniqueString
     
END

Numeric to Alphanumeric (Case insensitive)

CREATE FUNCTION dbo.GetUniqueCharAlaphNumCase(@Num AS BIGINT)
RETURNS VARCHAR(100)
BEGIN

      DECLARE @R AS INT
      DECLARE @UniqueString AS VARCHAR(100) = ''
     
      WHILE @Num <> 0 BEGIN
            SET @R = @Num % 36
            SET @Num = @Num / 36
           
            SET @UniqueString = @UniqueString + CHAR(CASE
                  WHEN @R BETWEEN 0 AND 9 
                  THEN @R + 48 
                  WHEN @R BETWEEN 10 AND 35
                  THEN @R + 55
            END)
      END
     
      RETURN @UniqueString
     
END

Numeric to Alphanumeric (Case sensitive includes spherical characters) 

CREATE FUNCTION dbo.GetUniqueChar(@Num AS BIGINT)
RETURNS VARCHAR(100)
BEGIN

      DECLARE @R AS INT
      DECLARE @UniqueString AS VARCHAR(100) = ''
     
      WHILE @Num <> 0 BEGIN
            SET @R = @Num % 95
            SET @Num = @Num / 95
           
            SET @UniqueString = @UniqueString + CHAR(32 + @R)
      END
     
      RETURN @UniqueString
     
END


Jun 11, 2015

How to remove comment from SQL script \ procedures SQL server

Sometimes there is a need to remove or delete comment part of SQL script from procedures, functions etc in SQL server. We can do this using below SQL function: