22,209
社区成员
发帖
与我相关
我的任务
分享
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-26 17:49:02
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([村名] NVARCHAR(10),[DDD] INT)
INSERT [tb]
SELECT N'渡口村',NULL UNION ALL
SELECT N'东升村',NULL UNION ALL
SELECT N'东升村',NULL UNION ALL
SELECT N'东升村',NULL UNION ALL
SELECT N'东升村',NULL UNION ALL
SELECT N'东升村',NULL UNION ALL
SELECT N'郑村',NULL
GO
--SELECT * FROM [tb]
-->SQL查询如下:
declare @i int
set @i=0
update tb set DDD=@i,@i=@i+1 where 村名 like '东升%'
select * from tb
/*
村名 DDD
---------- -----------
渡口村 NULL
东升村 1
东升村 2
东升村 3
东升村 4
东升村 5
郑村 NULL
(7 行受影响)
*/
if (OBJECT_ID('tb') is not null) drop table tb
Create table tb
(
banhao VARCHAR(20),
BBB VARCHAR(20) NULL
)
GO
INSERT INTO tb
SELECT 'AB7-3',NULL
UNION ALL
SELECT 'AB7-8',NULL
UNION ALL
SELECT 'AB7-32',NULL
UNION ALL
SELECT 'AB18-15',NULL
UNION ALL
SELECT 'AB18-2',NULL
SELECT banhao,RIGHT(banhao,LEN(banhao)-CHARINDEX('-',banhao)) BBB FROM tb
update tb set bbb=stuff(小班号,1,charindex('-',小班号),'')
Update t--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-26 17:42:30
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)
-- Blog : http://blog.csdn.net/htl258
--------------------------------------------------------------------------
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([小班号] NVARCHAR(10),[BBB] INT)
INSERT [tb]
SELECT N'AB7-3',23 UNION ALL
SELECT N'AB7-8',NULL UNION ALL
SELECT N'AB7-32',NULL UNION ALL
SELECT N'AB18-15',NULL UNION ALL
SELECT N'AB18-2',NULL
GO
--SELECT * FROM [tb]
-->SQL查询如下:
update tb set bbb=stuff(小班号,1,charindex('-',小班号),'')
--查询更新结果
select * from tb
/*
小班号 BBB
---------- -----------
AB7-3 3
AB7-8 8
AB7-32 32
AB18-15 15
AB18-2 2
(5 行受影响)
*/
update tb
set BBB=right(小班号,len(小班号)-patindex('%-%',小班号))
update tb set bbb=stuff(小班号,1,charindex('-',小班号),'')