Help with while loop in sql server

Question

I have this query Declare @Fnum nchar(5) While (len(@Fnum) 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?

Varun Setia · Answer

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

Pankaj Patel · Answer

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, '')) 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!

Sachin Singh · Answer

Declare @Fnum nvarchar(5)= '' While (len(@Fnum) Begin set @Fnum += '0' End select @Fnum

Varun Setia · Answer

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)) Begin SET @Fnum= CAST (CONCAT(@Fnum, '0' ) AS nvarchar(5)) --PRINT @Fnum End PRINT @Fnum

Mageshwaran R · Answer

Hi Alex, Try this query: DECLARE @Fnum nvarchar(5) SET @Fnum=1 WHILE ( @Fnum BEGIN PRINT 'The Fnum value is = ' + CONVERT ( VARCHAR ,@Fnum) SET @Fnum = @Fnum + 1 END

Mageshwaran R · Answer

Try this: DECLARE @Fnum nvarchar(5) SET @Fnum= '' WHILE ( @Fnum 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

Kamil Krupski · Answer

declare @Fnum as nvarchar() and set default value empty. Declare @Fnum nvarchar(5) = ''