select mainsys,tagname, substring(tagname,1,CHARINDEX('-',tagname)+3) as minsys from tb
select MainSys , TagName , substring(TagName,1,8) as MinSys from 表
select mainsys,tagname, substring(tagname,1,CHARINDEX('-',tagname)+3) as minsys from tb
--> 测试数据: @a declare @a table (MainSys varchar(3),TagName varchar(25)) insert into @a select 'UPW','UPW1-F10-RUP-FQI201-PV' union all select 'UPW','UPW1-F10-RUT-LISAHL201-PV' union all select 'UPW','UPW1-F20-CDP-CDP-PV' union all select 'UPW','UPW1-F20-EHT-TI1100-PV' select *,MinSys=substring(tagname,0,charindex('-',tagname)+charindex('-',substring(tagname,charindex('-',tagname)+1,len(tagname)))) from @a
--> 生成测试数据: @T DECLARE @T TABLE (MainSys VARCHAR(3),TagName VARCHAR(25)) INSERT INTO @T SELECT 'UPW','UPW1-F10-RUP-FQI201-PV' UNION ALL SELECT 'UPW','UPW1-F10-RUT-LISAHL201-PV' UNION ALL SELECT 'UPW','UPW1-F20-CDP-CDP-PV' UNION ALL SELECT 'UPW','UPW1-F20-EHT-TI1100-PV'--SQL查询如下:DECLARE @n INT; SET @n = 2;SELECT A.MainSys, TagName, STUFF(C.xmldoc.value('.','varchar(50)'),1,1,'') AS MinSys FROM @T AS A CROSS APPLY ( SELECT T.v.query('//v[position() <= sql:variable("@n")]') AS x FROM (SELECT CONVERT(XML,'<v>'+REPLACE(TagName,'-','</v><v>')+'</v>') AS v) AS T ) AS B CROSS APPLY ( SELECT xmldoc = ( SELECT '-' + T.x.value('.','varchar(20)') AS [text()] FROM B.x.nodes('//v') AS T(x) FOR XML PATH(''),TYPE ) ) AS C/* MainSys TagName MinSys ------- ------------------------- -------------------------------------------------- UPW UPW1-F10-RUP-FQI201-PV UPW1-F10 UPW UPW1-F10-RUT-LISAHL201-PV UPW1-F10 UPW UPW1-F20-CDP-CDP-PV UPW1-F20 UPW UPW1-F20-EHT-TI1100-PV UPW1-F20(4 行受影响)*/
substring(tagname,1,CHARINDEX('-',tagname)+3) as minsys
from tb
select MainSys , TagName , substring(TagName,1,8) as MinSys from 表
substring(tagname,1,CHARINDEX('-',tagname)+3) as minsys
from tb
--> 测试数据: @a
declare @a table (MainSys varchar(3),TagName varchar(25))
insert into @a
select 'UPW','UPW1-F10-RUP-FQI201-PV' union all
select 'UPW','UPW1-F10-RUT-LISAHL201-PV' union all
select 'UPW','UPW1-F20-CDP-CDP-PV' union all
select 'UPW','UPW1-F20-EHT-TI1100-PV'
select *,MinSys=substring(tagname,0,charindex('-',tagname)+charindex('-',substring(tagname,charindex('-',tagname)+1,len(tagname)))) from @a
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-07-03 09:38:26
-------------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (MainSys VARCHAR(3),TagName VARCHAR(25))
INSERT INTO @T
SELECT 'UPW','UPW1-F10-RUP-FQI201-PV' UNION ALL
SELECT 'UPW','UPW1-F10-RUT-LISAHL201-PV' UNION ALL
SELECT 'UPW','UPW1-F20-CDP-CDP-PV' UNION ALL
SELECT 'UPW','UPW1-F20-EHT-TI1100-PV'--SQL查询如下:DECLARE @n INT;
SET @n = 2;SELECT
A.MainSys,
TagName,
STUFF(C.xmldoc.value('.','varchar(50)'),1,1,'') AS MinSys
FROM @T AS A
CROSS APPLY
(
SELECT T.v.query('//v[position() <= sql:variable("@n")]') AS x
FROM (SELECT CONVERT(XML,'<v>'+REPLACE(TagName,'-','</v><v>')+'</v>') AS v) AS T
) AS B
CROSS APPLY
(
SELECT xmldoc = (
SELECT '-' + T.x.value('.','varchar(20)') AS [text()]
FROM B.x.nodes('//v') AS T(x)
FOR XML PATH(''),TYPE
)
) AS C/*
MainSys TagName MinSys
------- ------------------------- --------------------------------------------------
UPW UPW1-F10-RUP-FQI201-PV UPW1-F10
UPW UPW1-F10-RUT-LISAHL201-PV UPW1-F10
UPW UPW1-F20-CDP-CDP-PV UPW1-F20
UPW UPW1-F20-EHT-TI1100-PV UPW1-F20(4 行受影响)*/