使用案例-SQL Server更新记录

Question

我的原始表具有这样的值：

   ID           TELPHONE
  ----------- --------------
  1923        6276572   
  1949        2126670825
  2125
  1926        12126670825

我正在执行以下查询以获得预期结果：

UPDATE #T  
SET     TELEPHONE = CASE  
                    WHEN CHARINDEX('1212', TELEPHONE, 1) = 0 THEN 
  '1212'+TELEPHONE
        WHEN CHARINDEX('212', TELEPHONE , 1) = 1 THEN '1'+TELEPHONE
        ELSE TELEPHONE
                END 
 WHERE (STATE LIKE '%UU%' OR STATE LIKE '%U&P%' OR STATE LIKE '%UTTER%') AND 
LEN(TELEPHONE) <= 10 

我的期望：

 ID           TELPHONE
 ----------- --------------
 1923        12126276572   
 1949        12126670825
 2125
 1926        12126670825

但是，我在下面变得像(这是错误的(

 id      TELPHONE      
 ----------- --------------   
  1923        12126276572
 1949        12122126670825             
 2125        1212                         
 1926        12126670825

我在做什么错。以及RO如何获得预期结果？

Answer 1

我确实对此进行了正确更新(我省略了您的条件(：

  UPDATE TELE  SET TELPHONE = CASE WHEN CHARINDEX('212', TELPHONE , 1) = 1 THEN '1'+TELPHONE
                              WHEN CHARINDEX('1212', TELPHONE, 1) = 0 AND TELPHONE<>'' THEN '1212'+TELPHONE                            
                        ELSE TELPHONE
                        END

;输出：

+------+-------------+
|  ID  |  TELPHONE   |
+------+-------------+
| 1923 | 12126276572 |
| 1949 | 12126670825 |
| 2125 |             |
| 1926 | 12126670825 |
+------+-------------+

我认为，如果您可以添加到条件和电话＆lt;'''并简化情况(在我的情况下进行删除(，则表演会更好。

UPDATE TELE  SET TELPHONE = CASE WHEN CHARINDEX('212', TELPHONE , 1) = 1 THEN '1'+TELPHONE
                              WHEN CHARINDEX('1212', TELPHONE, 1) = 0   THEN '1212'+TELPHONE                               
                        ELSE TELPHONE
                        END                  
   WHERE --(STATE LIKE '%UU%' OR STATE LIKE '%U&P%' OR STATE LIKE '%UTTER%') 
       --AND 
        LEN(TELPHONE) <= 10 AND TELPHONE<>'';

Answer 2

这是因为您的 case在第一个表达式中匹配您不希望它的行。重新排列您的case，以便您对212的检查首先出现，因为这是一个正匹配，如果不满足，则只需在1212上检查负匹配：

declare @t table (
                 ID       int
                ,TELEPHONE nvarchar(50)
                 );
insert into @t
values (1923,'6276572'),
       (1949,'2126670825'),
       (2125,''),
       (1926,'12126670825');
select Telephone
        ,case when charindex('212',TELEPHONE,1) = 1
                then '1'+TELEPHONE
              when charindex('1212',TELEPHONE,1) = 0
                   and TELEPHONE <> ''
                then '1212'+TELEPHONE
              else TELEPHONE
              end as TelephoneNew
       ,charindex('212',TELEPHONE,1) as [212Match]
       ,charindex('1212',TELEPHONE,1) as [1212Match]
from @t

输出：

+-------------+--------------+----------+-----------+
|  Telephone  | TelephoneNew | 212Match | 1212Match |
+-------------+--------------+----------+-----------+
|     6276572 |  12126276572 |        0 |         0 |
|  2126670825 |  12126670825 |        1 |         0 |
|             |              |        0 |         0 |
| 12126670825 |  12126670825 |        2 |         1 |
+-------------+--------------+----------+-----------+