Convert C# to sql

Question

how to convert this to Sql Function

string Chars = "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ";
string R = "";
long N = 324535354; // param
do { R += Chars[(int)(N % 0x3E)]; } while ((N /= 0x3E) != 0);
return R;

Answer 1

Try this function:

create or alter function MyFunction( @N bigint ) 
returns varchar(max)
as
begin
    declare @R varchar(max) = ''
    declare @Chars varchar(max) = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'

    while 1 = 1
    begin
        set @R += substring( @Chars, (@N % len(@Chars)) + 1, 1)
        set @N /= len(@Chars)
        if @N = 0 break
    end

    return @R
end

Example:

select dbo.MyFunction( 324535354 )

Answer 2

T-SQL doesn't have a DO control flow statement but one can provide the same functionally with a BREAK condition on the exit criteria:

CREATE OR ALTER FUNCTION dbo.Example(@N bigint)
RETURNS varchar(100)
AS
BEGIN
    DECLARE @Chars varchar(62) = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE @R varchar(50) = '';
    WHILE 1 = 1
    BEGIN
        SET @R += SUBSTRING(@Chars, @N % CAST(0x3e AS int) + 1, 1);
        SET @N /= CAST(0x3e AS int);
        IF @N = 0 BREAK;
    END;
    RETURN @R;
END;
GO

Example usage:

SELECT dbo.Example(324535354);

Answer 3

Hi，@Ebrahim El-Sayed
We can use the BREAK keyword to exit the loop,as Viorel and DanGuzman answered above.
In this situation, we can also modify the WHILE conditional judge statement to achieve the desired result.
Please check this:

IF OBJECT_ID('dbo.udf_test')IS NOT NULL   
DROP FUNCTION dbo.udf_test;  
GO   
CREATE FUNCTION dbo.udf_test(@N bigint)  
RETURNS varchar(100)  
AS  
 BEGIN  
     DECLARE @Chars varchar(100) = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';  
     DECLARE @R varchar(100) = '';  
  WHILE @N != 0  
  BEGIN  
  SET @R = @R + SUBSTRING(@Chars, @N % CAST(0x3e AS int) + 1, 1);  
  SET @N = @N / CAST(0x3e AS int);  
  END;  
  RETURN @R;  
 END;  
 GO  
  
SELECT dbo.udf_test(324535354);  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

ALTER function [dbo].[Function] (@omarcanchanya varchar(max))
returns varchar(max)
as begin
-- Declare the return variable here
declare
@input VARCHAR(100),
@output VARCHAR(100)

SET @input = '123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
SELECT @output = '';
WHILE @omarcanchanya <> 0
BEGIN
SET @output = @output + SUBSTRING(@input,(@omarcanchanya % 62),1);
SET @omarcanchanya /= 62;
end

return @output

END