如何使用case语句更新值?

I have created update statement like below

我创建了如下所示的update语句

    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

但是我想用上面的语句更新更多的值，例如，如果想要更新价格=123，它必须更新112233，如果价格=456，它必须更新445566，如果价格=725，它必须更新772255，如何实现这个帮助我

3 个解决方案

#1

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.

如果有用的话，请告诉我。

#2

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:

你会使用这个函数和Meysam Tolouee的答案一样

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.

为了解释为什么函数的返回类型是varchar，这是因为它保证函数返回结果，不管输入的[合理]长度是多少。之所以选择20的最大长度，仅仅是因为输入[假定为]int值，而正int值最多为10位。

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

然而，varchar(20)是否会转变为SalePrice3的类型则是另一回事，应该单独考虑。

#3

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

Youy必须创建一个过程来实现所需的结果，而不是使用单个查询。

#1

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