--建表 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table_1]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[Table_1]( [ID] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] END go --插入测试数据 insert into [Table_1]([UserName])values('asd_1'); insert into [Table_1]([UserName])values('asd_2'); insert into [Table_1]([UserName])values('asd_3'); insert into [Table_1]([UserName])values('a阿斯顿sd_4'); insert into [Table_1]([UserName])values('asd_5'); insert into [Table_1]([UserName])values('6'); insert into [Table_1]([UserName])values('as阿斯顿飞d_7'); insert into [Table_1]([UserName])values('asd_8'); --查看数据 select * from table_1 --更新每次修改的数据 update table_1 set UserName='asd_1' where id=1; update table_1 set UserName='asd_2' where id=2; update table_1 set UserName='asd_3' where id=3; update table_1 set UserName='a阿斯顿sd_4' where id=4; update table_1 set UserName='asd_5' where id=5; update table_1 set UserName='6' where id=6; update table_1 set UserName='as阿斯顿飞d_7' where id=7; update table_1 set UserName='asd_8' where id=8; --采用stuff删除记录的前缀 update table_1 set UserName=stuff(UserName,1,charindex('_',UserName),'') --采用substring删除记录的前缀 update table_1 set UserName=substring(UserName,charindex('_',UserName)+1,len(UserName)); --采用replace删除记录的前缀 update table_1 set UserName=replace(UserName,substring(UserName,1,charindex('_',UserName)),'');