SQL Server How to create a new column with case or update an old column?

42fyovps 于 2023-03-28 发布在其他

关注(0)|答案(1)|浏览(185)

I have a table googleplaystore and column Size. I had to remove the last letters from the column Size and I want to drop the old column Size and Save a new one based on case. How do you save a column from case to a new column? I also need to convert values with k (kilobytes) into MB.

SELECT Size,
CASE
WHEN RIGHT(Size,1) = 'k' OR RIGHT(Size,1) = 'M'  THEN SUBSTRING(Size,1,LEN(Size)-1)
ELSE 'Varies with device'
END
FROM googleplaystore

sql-server

来源：https://stackoverflow.com/questions/75847713/how-to-create-a-new-column-with-case-or-update-an-old-column

1条答案

按热度按时间

f0ofjuux1#

You can do it as follows :

Update googleplaystore
    set size = CASE
    WHEN RIGHT(Size,1) = 'k' THEN CAST(SUBSTRING(Size,1,LEN(Size)-1) as float)/ cast(1024 as float)
    WHEN RIGHT(Size,1) = 'M' THEN SUBSTRING(Size,1,LEN(Size)-1)
    ELSE size 
END 
from googleplaystore

to convert from kb to MB in this case you need to divide by 1024.

Input :

size
1000K
1000M
2000

Result :

size
0.976563
1000
2000

Demo here

赞(0）回复(0）举报 2023-03-28

我来回答

SQL Server How to create a new column with case or update an old column?

1条答案

相关问题

热门标签

最新问答