Help with while loop in sql server

Schleid Alex

5y975Learn iOS Programming

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?

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Varun Setia5y ago
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
+2
Mageshwaran R5y ago
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
0
Varun Setia5y ago
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
+1
Pankajkumar Patel5y ago
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!
+2
Sachin Singh5y ago
Declare @Fnum nvarchar(5)=''
While (len(@Fnum)<5)

    Begin
     set @Fnum += '0'

    End

select @Fnum
+2
Mageshwaran R5y ago
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
+1
Kamil Krupski5y ago
declare @Fnum as nvarchar() and set default value empty.

Declare @Fnum nvarchar(5) = ''
0