27,579
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([FullName] nvarchar(37))
Insert #T
select N'abcd-测试截取内容(afadf' union all
select N'大白兔-奶糖' union all
select N'凉茶(加多宝)'
Go
--测试数据结束
UPDATE #T SET FullName = SUBSTRING(FullName, CHARINDEX('-', FullName) + 1,
CASE WHEN CHARINDEX('(', FullName) = 0 THEN LEN(FullName)
ELSE CHARINDEX('(', FullName)
END - CHARINDEX('-', FullName)
- CASE WHEN CHARINDEX('-', FullName) > 0
AND CHARINDEX('(', FullName) = 0 THEN 0
ELSE 1
END)
[/quote]UPDATE #T SET FullName = SUBSTRING(FullName, CHARINDEX('-', FullName) + 1,
CASE WHEN CHARINDEX('(', FullName) = 0 THEN LEN(FullName)
ELSE CHARINDEX('(', FullName)
END - CHARINDEX('-', FullName)
- CASE WHEN CHARINDEX('-', FullName) > 0
AND CHARINDEX('(', FullName) = 0 THEN 0
ELSE 1
END)
[/quote]UPDATE #T SET FullName = SUBSTRING(FullName, CHARINDEX('-', FullName) + 1,
CASE WHEN CHARINDEX('(', FullName) = 0 THEN LEN(FullName)
ELSE CHARINDEX('(', FullName)
END - CHARINDEX('-', FullName)
- CASE WHEN CHARINDEX('(', FullName) = 0 THEN 0
ELSE 1
END)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([FullName] nvarchar(37))
Insert #T
select N'abcd-测试截取内容(afadf' union all
select N'大白兔-奶糖' union all
select N'凉茶(加多宝)'
Go
--测试数据结束
UPDATE #T SET FullName = SUBSTRING(FullName, CHARINDEX('-', FullName) + 1,
CASE WHEN CHARINDEX('(', FullName) = 0 THEN LEN(FullName)
ELSE CHARINDEX('(', FullName)
END - CHARINDEX('-', FullName)
- CASE WHEN CHARINDEX('-', FullName) > 0
AND CHARINDEX('(', FullName) = 0 THEN 0
ELSE 1
END)
[/quote]
查出来只影响了3行的,但这边是需要修改列中所有类似名称,不好意思,真菜鸟一枚,费心了....[/quote]
……把这个语句换成楼主自己的表名和字段名就行了#T是我用来测试用的,一共三条数据,楼主的有多少数据会更新多少数据
UPDATE #T SET FullName = SUBSTRING(FullName, CHARINDEX('-', FullName) + 1,
CASE WHEN CHARINDEX('(', FullName) = 0 THEN LEN(FullName)
ELSE CHARINDEX('(', FullName)
END - CHARINDEX('-', FullName)
- CASE WHEN CHARINDEX('-', FullName) > 0
AND CHARINDEX('(', FullName) = 0 THEN 0
ELSE 1
END)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([FullName] nvarchar(37))
Insert #T
select N'abcd-测试截取内容(afadf' union all
select N'大白兔-奶糖' union all
select N'凉茶(加多宝)'
Go
--测试数据结束
UPDATE #T SET FullName = SUBSTRING(FullName, CHARINDEX('-', FullName) + 1,
CASE WHEN CHARINDEX('(', FullName) = 0 THEN LEN(FullName)
ELSE CHARINDEX('(', FullName)
END - CHARINDEX('-', FullName)
- CASE WHEN CHARINDEX('-', FullName) > 0
AND CHARINDEX('(', FullName) = 0 THEN 0
ELSE 1
END)
[/quote]
查出来只影响了3行的,但这边是需要修改列中所有类似名称,不好意思,真菜鸟一枚,费心了....--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([FullName] nvarchar(37))
Insert #T
select N'abcd-测试截取内容(afadf' union all
select N'大白兔-奶糖' union all
select N'凉茶(加多宝)'
Go
--测试数据结束
UPDATE #T SET FullName = SUBSTRING(FullName, CHARINDEX('-', FullName) + 1,
CASE WHEN CHARINDEX('(', FullName) = 0 THEN LEN(FullName)
ELSE CHARINDEX('(', FullName)
END - CHARINDEX('-', FullName)
- CASE WHEN CHARINDEX('-', FullName) > 0
AND CHARINDEX('(', FullName) = 0 THEN 0
ELSE 1
END)
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([FullName] nvarchar(37))
Insert #T
select N'abcd-测试截取内容(afadf' union all
select N'大白兔-奶糖' union all
select N'凉茶(加多宝)'
Go
--测试数据结束
SELECT SUBSTRING(FullName, CHARINDEX('-', FullName) + 1,
CASE WHEN CHARINDEX('(', FullName) = 0 THEN LEN(FullName)
ELSE CHARINDEX('(', FullName)
END - CHARINDEX('-', FullName)
- CASE WHEN CHARINDEX('-', FullName) > 0
AND CHARINDEX('(', FullName) = 0 THEN 0
ELSE 1
END) AS name
FROM #T;