declare @s varchar(10) set @s='测试/字符' select left(@s,charindex('/',@s)-1)/*
---------- 测试(所影响的行数为 1 行)*/
update tb set col=stuff(col,charindex('/',col),len(col)-charindex('/',col)+1,'') where charindex('/',col)>0
update tb set col=replace(col,substring(charindex('/',col),len(col)-charindex('/',col)+1),'') where charindex('/',col)>0
--修改 ---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-12-04 15:56:08 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(10)) insert [tb] select '测试/字符1' union all select '测试/字符2' union all select '测试/字符3' --------------开始查询-------------------------- update tb set col=replace(col,substring(col,charindex('/',col),len(col)-charindex('/',col)+1),'') where charindex('/',col)>0select * from tb ----------------结果---------------------------- /* col ---------- 测试 测试 测试(3 行受影响) */
update tb set 字段=left(字段,charindex('/',字段)-1 )
declare @col varchar(10) set @col ='测试/字符' select left(@col,charindex('/',@col)-1)update tb_name set col =left(col,charindex('/',col)-1) where charindex('/',col)>0
set @s='测试/字符'
select left(@s,charindex('/',@s)-1)/*
----------
测试(所影响的行数为 1 行)*/
set col=stuff(col,charindex('/',col),len(col)-charindex('/',col)+1,'')
where charindex('/',col)>0
tb
set
col=replace(col,substring(charindex('/',col),len(col)-charindex('/',col)+1),'')
where
charindex('/',col)>0
----------------------------------------------------------------
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-04 15:56:08
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(10))
insert [tb]
select '测试/字符1' union all
select '测试/字符2' union all
select '测试/字符3'
--------------开始查询--------------------------
update
tb
set
col=replace(col,substring(col,charindex('/',col),len(col)-charindex('/',col)+1),'')
where
charindex('/',col)>0select * from tb
----------------结果----------------------------
/* col
----------
测试
测试
测试(3 行受影响)
*/
update tb
set 字段=left(字段,charindex('/',字段)-1 )
set @col ='测试/字符'
select left(@col,charindex('/',@col)-1)update tb_name
set col =left(col,charindex('/',col)-1)
where charindex('/',col)>0