Help with while loop in sql server

Question

I have this query

 

Declare @Fnum nchar(5)

 

While (len(@Fnum)<5

Begin

 Set @Fnum += '0

End

 Print @Fnum

 

I am expecting 0000 as result

 

 But this is not happening, I only have 0 as result?

 

Can I have some help please?

 

Answer 1

For generating repeated characters one function named REPLICATE is available in SQL Server , below is the code

 

Declare @Fnum nvarchar(5)
SET @Fnum= REPLICATE('0',5)
PRINT @Fnum

 

Answer 2

Hi Schleid Alex,

 

You can do your mentioned scenarion in following 3 ways:

 

(1) declare @Fnum nvarchar(5)=''

• Set '' at declaration time.

(2) while (len(isnull(@Fnum, '')) < 5)

• check null at length check time.
• First approach is better than this one.

(3) select right('00000' + cast(isnull(@Fnum, '') as nvarchar(5)), 5)

• You can do padding by this way instead of while loop.
• This approach is better than above both way.

Note

• For all above approaches you must have to use nvarchar instead of nchar

Hope, this will help you!

Answer 3

Declare @Fnum nvarchar(5)=''
While (len(@Fnum)<5)

    Begin
     set @Fnum += '0'

    End

 select @Fnum

Answer 4

 Problem is with nchar make it and cast it to nvarchar, also intialize it, otherwise it will be null.

Also, you are working with string so use CONCAT  

Declare @Fnum nvarchar(5)=''

While (len(@Fnum))<5
Begin
 SET @Fnum=CAST(CONCAT(@Fnum,'0') AS nvarchar(5))
 --PRINT @Fnum
End
 PRINT @Fnum

 

 

 

 

Answer 5

Hi Alex,

 

Try this query:

 

DECLARE @Fnum nvarchar(5)
SET @Fnum=1
WHILE ( @Fnum < 5)
BEGIN
    PRINT 'The Fnum value is = ' + CONVERT(VARCHAR,@Fnum)
    SET @Fnum  = @Fnum  + 1
END

 

Answer 6

Try this:

 

DECLARE @Fnum nvarchar(5)
SET @Fnum=''
WHILE ( @Fnum < 5)
BEGIN
   if(@Fnum='')
   begin
  SET @Fnum='0000'
    PRINT 'The Fnum value is = ' + CONVERT(VARCHAR,@Fnum)
    end
    else
    begin
    SET @Fnum  =  CAST(LEFT('0000', LEN('0000')-LEN(CAST(@Fnum+1 AS NVARCHAR(5)))) +  CAST(@Fnum+1 AS NVARCHAR(5)) AS NVARCHAR(5))
     PRINT 'The Fnum value is = ' + CONVERT(VARCHAR,@Fnum)
    end
END

 

Answer 7

declare @Fnum as nvarchar() and set default value empty.

 

Declare @Fnum nvarchar(5) = ''