ID 数量
A02025015000000000
F0110022BF175111A0 0022
F0210242BF022111A0 0242将ID栏目中以F开头的ID号中的第5位至第8位数据提取出来赋给数量?
A02025015000000000
F0110022BF175111A0 0022
F0210242BF022111A0 0242将ID栏目中以F开头的ID号中的第5位至第8位数据提取出来赋给数量?
tb
set
数量=substring(id,5,4)
update tb
set 数量=cast(substring(ID,5,4) as int)
where ID like 'F%'
update tb
set 数量=cast(substring(ID,5,4) as int)
where ID like 'F%'
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-31 16:02:45
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(18),[数量] sql_variant)
insert [tb]
select 'A02025015000000000',null union all
select 'F0110022BF175111A0',null union all
select 'F0210242BF022111A0',null
--------------开始查询--------------------------
update
tb
set
数量=substring(id,5,4)
select * from tb
----------------结果----------------------------
/* ID 数量
------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A02025015000000000 2501
F0110022BF175111A0 0022
F0210242BF022111A0 0242(3 行受影响)*/
set 数量=substring(id,5,4)
where left(id,1)='F'
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-31 16:02:45
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(18),[数量] sql_variant)
insert [tb]
select 'A02025015000000000',null union all
select 'F0110022BF175111A0',null union all
select 'F0210242BF022111A0',null
--------------开始查询--------------------------
update
tb
set
数量=substring(id,5,4)
where
ID like 'F%'
select * from tb
----------------结果----------------------------
/* ID 数量
------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A02025015000000000 NULL
F0110022BF175111A0 0022
F0210242BF022111A0 0242(3 行受影响)*/
go
create table [tb]([ID] varchar(18),[数量] sql_variant)
insert [tb]
select 'A02025015000000000',null union all
select 'F0110022BF175111A0',null union all
select 'F0210242BF022111A0',nullupdate tb set 数量=substring(id,5,4)select * from tbID 数量
------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
A02025015000000000 2501
F0110022BF175111A0 0022
F0210242BF022111A0 0242(3 行受影响)
set 数量=substring(ID,5,4)
where ID like 'F%'
update tb set
数量=cast(substring(ID,5,4) as int)
where ID like 'F%'
数量=substring(ID,5,4)
where ID like 'F%'
and isnumeric(substring(ID,5,4))=1 --判断取到的字符是否为数值