CODEWITHSUNDEEP
sqlsql-server
HermannHH
HermannHH

Reputation: 1772

Complicated SQL while loop

I am trying to create a while loop in SQL and it seems kind of complex. Here's what I need it to achieve:

  1. Iterate through a single VARCHAR string (ex. '123')
  2. If the nth character is in an even position in the string (ex. 2nd, 4th .... letter in the string), it must be added(SUM) to a base variable (Let's assume @z)
  3. If the nth character is in an odd position in the string (ex. 1st, 3rd .... letter in the string), it must be multiplied by 2. If this newly generated value (Let's assume @y) is less than 10, it must be added(SUM) to the base variable (Still the same assumed @z). If @y is greater than 10, we need to subtract 9 from @y before adding(SUM) it to @z

After iterating through the entire string, this should return a numeric value generated by the above process.

Here is what I've done so far, but I'm stuck now (Needless to say, this code does not work yet, but I think I'm heading in the right direction):

DECLARE @x varchar(20) = '12345'
DECLARE @p int = len(@x)

WHILE @p > 0
SELECT @x = 
     stuff(@x, @p, 1, 
      case when CONVERT(INT,substring(@x, @p, 1)) % 2 = 0 then CONVERT(INT, @x) + CONVERT(INT,substring(@x, @p, 1))
      end), @p -= 1

RETURN @x;

PS. The input will always be 100% numeric values, but it is formatted as VARCHAR when I recieve it.

UPDATE

The expected result for the sample string is 15

Upvotes: 3

Views: 441

Answers (4)

Julien Vavasseur
Julien Vavasseur

Reputation: 3962

The while loop does not seem necessary here. This can be achieved with a CTE that will split the string and a case statement:

DECLARE @x varchar(20) = '12345';

with split(id, v) as (
    select 0, cast(0 as tinyint)
    union all
    select id+1, cast(SUBSTRING(@x, id+1, 1) as tinyint)
    From split
    Where id+1 <= len(@x)
)
Select Result = SUM(
    Case When id % 2 = 0 then v
        When v < 5 then v*2
        Else (v*2)-9
    End
    )
From split

output = 15

Upvotes: 1

Raj Kamuni
Raj Kamuni

Reputation: 388

    DECLARE @x INT = 12345
DECLARE @p int = len(@x)
DECLARE @z INT =0
PRINT @p%2
SET @x=@x/10
PRINT @x
WHILE @p > 0
BEGIN
  IF(@p%2 = 0)
  BEGIN
  SET @z=@z+@x%10
  SET @p=@p-1
 SET @x=@x/10
 END
 ELSE
 BEGIN 
 SET @z=@z+(2*(@x%10)) 
 SET @p=@p-1
 SET @x=@x/10
 IF(@x>=10)
 BEGIN
 SET @x=(@x/10+@x%10)
 END
 END
 END
 SELECT @z

Upvotes: 1

Ravi
Ravi

Reputation: 475

Check below if it helps you

DECLARE @x varchar(20) = '12345'
DECLARE @p int = 1
DECLARE @result bigint=0;
DECLARE @tempval int =0;

WHILE @p <= len(@x)
BEGIN
SET @tempval = CONVERT(INT,substring(@x, @p, 1));    
if(@p%2 = 1)
BEGIN
    SET @tempval = @tempval * 2;
    IF(@tempval >= 10) SET @tempval = @tempval - 9;
END
SET @result = @result + @tempval;

  SET @p = @p + 1;
 END;

PRINT @result;--This is the result
 RETURN @x;

Upvotes: 2

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can do this without using a loop. Here is a solution using Tally Table:

DECLARE @x VARCHAR(20) = '12345'
DECLARE @z INT = 0 -- base value

;WITH E1(N) AS( -- 10 ^ 1 = 10 rows
    SELECT 1 FROM(VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))t(N)
),
E2(N) AS(SELECT 1 FROM E1 a CROSS JOIN E1 b), -- 10 ^ 2 = 100 rows
E4(N) AS(SELECT 1 FROM E2 a CROSS JOIN E2 b), -- 10 ^ 4 = 10,000 rows
CteTally(N) AS(
    SELECT TOP(LEN(@x)) ROW_NUMBER() OVER(ORDER BY(SELECT NULL))
    FROM E4
),
CteChars(N, num) AS(
    SELECT
        t.N, CAST(SUBSTRING(@x, t.N, 1) AS INT)
    FROM CteTally t
    WHERE t.N <= LEN(@x)
)
SELECT
    SUM(
        CASE
            WHEN N % 2 = 0 THEN num
            WHEN num * 2 < 10 THEN num * 2
            ELSE (num * 2) - 9
        END
    ) + @z
FROM CteChars

The CTEs up to CteTally generates a list of number from 1 to LEN(@x). CteChars breaks @x character by character into separate rows. Then the final SELECT does a SUM based on the conditions.

OUTPUT : 15

Upvotes: 3

Related Questions