CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlcase-when
user3078299
user3078299

Reputation: 19

How to update values using case statement

I have created update statement like below

    UPDATE  dbo.S_Item
    SET     SalePrice3 = CASE WHEN Price <0 THEN '-1'
        when Price=1 then 11
        when Price=2 then 22
        when Price=3 then 33
        when Price=4 then 44
        when Price=5 then 55
        when Price=6 then 66
        when Price=7 then 77
        when Price=8 then 88
        when Price=9 then 99
        when Price=0 then 00     
     end

but i want update more values using above statement for example if want update price=123 it has to update 112233,if price=456 it has to update 445566,if price=725 it has to update 772255 how can achieve this help me

Upvotes: 1

Views: 403

Answers (3)

Andriy M
Andriy M

Reputation: 77687

If the point is just in duplication of every digit, here's another implementation of the duplication method:

CREATE FUNCTION dbo.DuplicateDigits(@Input int)
RETURNS varchar(20)
AS
BEGIN
  DECLARE @Result varchar(20) = CAST(@Input AS varchar(20));
  DECLARE @Pos    int         = LEN(@Result);
  WHILE @Pos > 0
  BEGIN
    SET @Result = STUFF(@Result, @Pos, 0, SUBSTRING(@Result, @Pos, 1));
    SET @Pos -= 1;
  END;
  RETURN @Result;
END;

The method consists in iterating through the digits backwards, extracting each using SUBSTRING and duplicating it using STUFF.

And you would be using this function same as in Meysam Tolouee's answer:

UPDATE  dbo.S_Item
SET     SalePrice3 = CASE
                         WHEN Price < 0 THEN '-1'
                         ELSE dbo.DuplicateDigits(SalePrice3)
                     END;

To explain a little why the function's returned type is varchar, it is because that guarantees that the function returns the result no matter what the input's [reasonable] length is. The maximum length of 20 has been chosen merely because the input is [assumed to be] int and positive int values consist of up to 10 digits.

However, whether varchar(20) converts to the type of SalePrice3 is another matter, which should be considered separately.

Upvotes: 1

Meysam Tolouee
Meysam Tolouee

Reputation: 579

Create Function ReplicateDigits (
@Number Int)
Returns BigInt
    Begin

    Declare @Step SmallInt = 1,
        @Result nVaRchar(100) = N''

    While (@Step <= Len(@Number))
        Begin 
        Select @Result = @Result + Replicate(SubString(Cast(@Number As Varchar), @Step, 1), 2)
        Select @Step = @Step + 1
        End

    Return Cast(@Result As BigInt)

    End
Go

Then:

  UPDATE    dbo.S_Item
        SET SalePrice3 = CASE
                           WHEN Price <0 THEN '-1'
                           Else dbo.ReplicateDigits(Price)
                         End

Let me know if it was useful.

Upvotes: 3

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Youy Must Create a Procedure for Achiving the Desired Result Rather Than to Use a Single Query.

Upvotes: 0

Related Questions