字段 ID
字段 NAME 内容
ID NAME
1 p_lihao
2 p_caitou
3 p_xiaoming
。怎么修改成
ID NAME
1 lihao
2 caitou
3 xiaoming
。
字段 NAME 内容
ID NAME
1 p_lihao
2 p_caitou
3 p_xiaoming
。怎么修改成
ID NAME
1 lihao
2 caitou
3 xiaoming
。
调试欢乐多
name = stuff(name,1,2,'');
name = stuff(name,1,2,'')
where name like 'p_%';
update tb set name = substring(name , charindex('-',name) + 1 , len(name)) where charindex('-',name) > 0如果是有规律的,就是1楼update tb set
name = stuff(name,1,2,'');
name = stuff(name,1,2,'')
where name like 'p$_%' escape '$';
insert into tb values(1 , 'p_lihao')
insert into tb values(2 , 'p_caitou')
insert into tb values(3 , 'p_xiaoming')
GOupdate tb set name = substring(name , charindex('_',name) + 1 , len(name)) where charindex('_',name) > 0
select * from tbdrop table tb /*
ID NAME
----------- --------------------
1 lihao
2 caitou
3 xiaoming(所影响的行数为 3 行)*/
-- Author : liangCK 小梁
-- Comment: 小梁 爱 兰儿
-- Date : 2009-09-06 11:02:05
-------------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (ID INT,NAME VARCHAR(10))
INSERT INTO @T
SELECT 1,'p_lihao' UNION ALL
SELECT 2,'p_caitou' UNION ALL
SELECT 3,'p_xiaoming' UNION ALL
SELECT 4,'liang'--SQL查询如下:update @T set
name = stuff(name,1,2,'')
where name like 'p$_%' escape '$';select * from @T/*
ID NAME
----------- ----------
1 lihao
2 caitou
3 xiaoming
4 liang(4 row(s) affected)
*/
name = stuff(name,1,2,'');
这个就可以了啊
name = stuff(name,1,2,'');
INSERT INTO @T
SELECT 1,'p_lihao' UNION ALL
SELECT 2,'p_caitou' UNION ALL
SELECT 3,'p_xiaoming' UNION AL
update @T set
name = stuff(name,1,2,'')
where name like 'p$_%' escape '$';select * from @T
update tb set name = replace(name,'p_','')
是不是都在开头有空格了啊。
那还是第一种号一点啊,直接截取