求语句.SQL如何把整数的最后一位全部改为9

Question

我的 表名为 ecs_goods 
    字段名为 shop_price

Answer 1

UPDATE ecs_goods SET RIGHT(RTRIM(shop_price),1)=9

Answer 2

create table ecs_goods 
(
shop_price int
)
insert ecs_goods
select 111 union
select 222update ecs_goods
set shop_price = substring(rtrim(shop_price),1,len(rtrim(shop_price)) - 1) + '9'select * from ecs_goods
drop table ecs_goods
结果:
119
229

Answer 3

UPDATE ecs_goods 
SET shop_price = replace(shop_price,right(shop_price,1),9)

Answer 4

update ecs_goods 
  set shop_price=reverse(stuff(reverse(rtrim(shop_price)),1,1,'9'))

Answer 5

update ecs_goods 
  set shop_price=round(shop_price,-1)+9

Answer 6

我晕..数据库里面有小数..得先把小数去了先啊
SQL去小数杂整呢

Answer 7

DECLARE @ecs_goods TABLE([shop_price] DECIMAL(10,2))
INSERT @ecs_goods 
SELECT 123 UNION ALL 
SELECT 1.23UPDATE @ecs_goods 
 SET [shop_price]=ROUND([shop_price],-1)+9
WHERE [shop_price]-FLOOR([shop_price])=0SELECT * FROM @ecs_goods
/*
shop_price
---------------------------------------
129.00
1.23
*/

Answer 8

看错了!!!
UPDATE ecs_goods SET SUBSTRING(shop_price,CHARINDEX('.',shop_price)-1,CHARINDEX('.',shop_price))=9 
这样

Answer 9

IF EXISTS(CHARINDEX('.',shop_price)>0)
BEGIN
    UPDATE ecs_goods SET SUBSTRING(CONVERT(VARCHAR,shop_price),CHARINDEX('.',shop_price)-1,1) WHERE CHARINDEX('.',@PRICE)>0
END
   ELSE 
BEGIN
      UPDATE ecs_goods SET RIGHT(RTRIM(shop_price),1)=9
END
这样试试?