34,576
社区成员
发帖
与我相关
我的任务
分享
select
case
when col='无' then 0
else left(col,1)
end as col
from tb
if exists (select * from sysobjects where id = OBJECT_ID('[tb_test]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [tb_test]
CREATE TABLE [tb_test] (
[col] [nchar] (10) NULL)
INSERT [tb_test] ([col]) VALUES ( N'2个')
INSERT [tb_test] ([col]) VALUES ( N'2只')
INSERT [tb_test] ([col]) VALUES ( N'3块')
INSERT [tb_test] ([col]) VALUES ( N'无')
INSERT [tb_test] ([col]) VALUES ( N'4块')
select
case
when col='无' then 0
else left(col,1)
end as col
from tb_test
结果:
--------
col
-----------
2
2
3
0
4
(5 行受影响)
if exists (select * from sysobjects where id = OBJECT_ID('[tb_test]') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
DROP TABLE [tb_test]
CREATE TABLE [tb_test] (
[col] [nchar] (10) NULL)
INSERT [tb_test] ([col]) VALUES ( N'2个')
INSERT [tb_test] ([col]) VALUES ( N'2只')
INSERT [tb_test] ([col]) VALUES ( N'3块')
INSERT [tb_test] ([col]) VALUES ( N'无')
INSERT [tb_test] ([col]) VALUES ( N'4块')
select case when isnumeric(LEFT([col],1))=1 then LEFT([col],1) else 0 end as [col]
from [tb_test]
col
2
2
3
0
4