kaprekar number check T-SQL problem

Question

question

Anim233-8415 asked May 8, '22 Zoehui-MSFT edited May 9, '22

kaprekar number check T-SQL problem

Hello, i made a function on T-SQL that checks if the number is a kaprekar one
CREATE FUNCTION [dbo].[kaprekar](@n int)
RETURNS bit
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @result bit;
DECLARE @sq_n INT;
DECLARE @count_digits INT;
DECLARE @r_digits INT;
DECLARE @eq_parts INT;
DECLARE @sum INT;
` SET @sq_n = @n * @n;`
SET @count_digits=0;
WHILE @sq_n <> 0
BEGIN
SET @count_digits= @count_digits + @count_digits;
SET @sq_n=@sq_n/10;
END;
SET @sq_n = @n*@n;

WHILE @r_digits<@count_digits
BEGIN
SET @eq_parts=POWER(10,@r_digits);
SET @sum = @sq_n/@eq_parts + @sq_n% @eq_parts;
END;
`
IF @n = 1
BEGIN
SET @result=1;
END;
ELSE IF @eq_parts = @n
BEGIN
SET @result=0;
` END;`
ELSE IF @sum=@n
BEGIN
SET @result=1;
` END;`
ELSE
BEGIN
SET @result=0;
END;
` RETURN @result;`
END;
``
But it don't work. when i insert any number except 1, it prints 0, which is not correct. i tried to analyze my code, and made a conclusion that the problem is with in the main code block, not with the massive IF ELSE block. can someone help?

sql-server-transact-sql

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-05-08T17:45:12.073Z

JingyangLi answered May 8, '22

 create or alter FUNCTION [dbo].[kaprekar](@n int)
 RETURNS bit
 AS
 BEGIN
 DECLARE @result bit;
 Begin
 If exists ( 
 select 1
 from 
 (select @n number,   square_value,   
 Cast(left(square_value,sq_len-n ) as int) number_left
 ,Cast(stuff(square_value,1,(sq_len-n ),'') as int) number_right 
 from (
 Select @n number,  len(square(@n)) sq_len  
 ,square(@n) square_value   )t
 Cross apply (Select Number FROM master.dbo.spt_values
 where type='P' and Number >=1 and Number <=1000 and Number< len(square(@n)) ) d(n)
 ) t
 where number_left+number_right=number
    
 )
 Select @result=1
 else 
 Select @result=0
 End
    
 RETURN @result; 
    
 END;
    
 /*
 select  [dbo].[kaprekar](9)
 --9 45 55 99 297 703 999
    
 */

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-05-08T17:36:09.767Z

TomCooper-6989 answered May 8, '22 TomCooper-6989 edited May 8, '22

One way to do it
CREATE FUNCTION [dbo].[kaprekar](@n int)
RETURNS bit
WITH EXECUTE AS CALLER
AS
BEGIN
Declare @nSqrd As bigint,
@nSqrdString As varchar(20),
@LennSqrd As bigint,
@Pointer As bigint,
@Result bit;
Set @Result = 0;
Set @nSqrd = Power(Cast(@n As bigint), 2);
Set @nSqrdString = @nSqrd;
Set @Pointer = 1;
If @n = 1
Begin
Set @Result = 1;;
End;
While @Pointer < Len(@nSqrdString) And @Result = 0
Begin
If Cast(Left(@nSqrdString, @Pointer) As bigint) + Cast(SubString(@nSqrdString, @Pointer + 1, Len(@nSqrdString)) As bigint) = @n
And Cast(SubString(@nSqrdString, @Pointer + 1, Len(@nSqrdString)) As bigint) > 0
Begin
Set @Result = 1;
End;
Set @Pointer = @Pointer + 1;
END;
Return @Result;
End;
go

Tom

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question