IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB GO CREATE TABLE TB( ID INT ,VAL VARCHAR(100) ) INSERT INTO TB SELECT 1 ,'BEGIN:VCARD NULL' UNION ALL SELECT 2 ,'VERSION:2.1 NULL' UNION ALL SELECT 3 ,'N CHARSET=UTF-8:' UNION ALL SELECT 4 ,'FN CHARSET=UTF-8:茵-东莞' UNION ALL SELECT 5 ,'TEL X-VOICE:13713026***' UNION ALL SELECT 6 ,'END:VCARD NULL' UNION ALL SELECT 7 ,'BEGIN:VCARD NULL' UNION ALL SELECT 8 ,'VERSION:2.1 NULL' UNION ALL SELECT 9 ,'N CHARSET=UTF-8:' UNION ALL SELECT 10 ,'FN CHARSET=UTF-8:变生' UNION ALL SELECT 11 ,'TEL X-VOICE:13556776***' UNION ALL SELECT 12 ,'END:VCARD NULL' ;WITH MU AS ( SELECT * ,(SELECT COUNT(1) FROM TB T2 WHERE T2.VAL LIKE 'BEGIN%' AND T2.ID<=T1.ID) AS NID FROM TB T1 ) SELECT LTRIM(NID)+':' +MAX(CASE WHEN VAL LIKE 'N %' THEN STUFF(VAL,1,2,'') ELSE NULL END) +MAX(CASE WHEN VAL LIKE 'FN %' THEN STUFF(VAL,1,CHARINDEX(':',VAL),'') ELSE NULL END) +MAX(CASE WHEN VAL LIKE 'TEL %' THEN STUFF(VAL,1,3,'') ELSE NULL END) FROM MU GROUP BY NID /* 1:CHARSET=UTF-8:茵-东莞 X-VOICE:13713026*** 2:CHARSET=UTF-8:变生 X-VOICE:13556776*** */
CREATE TABLE #TB( ID INT ,VAL VARCHAR(100) ) INSERT INTO #TB SELECT 1 ,'BEGIN:VCARD NULL' UNION ALL SELECT 2 ,'VERSION:2.1 NULL' UNION ALL SELECT 3 ,'N CHARSET=UTF-8:' UNION ALL SELECT 4 ,'FN CHARSET=UTF-8:茵-东莞' UNION ALL SELECT 5 ,'TEL X-VOICE:13713026***' UNION ALL SELECT 6 ,'END:VCARD NULL' UNION ALL SELECT 7 ,'BEGIN:VCARD NULL' UNION ALL SELECT 8 ,'VERSION:2.1 NULL' UNION ALL SELECT 9 ,'N CHARSET=UTF-8:' UNION ALL SELECT 10 ,'FN CHARSET=UTF-8:变生' UNION ALL SELECT 11 ,'TEL X-VOICE:13556776***' UNION ALL SELECT 12 ,'END:VCARD NULL'select row_id=row_number() over(order by t1.id asc),t1.name+' '+t2.name from (select id,val,name=stuff(val,1,3,'') from #tb where id%6=4) t1 inner join (select id,val,name=stuff(val,1,4,'') from #tb where id%6=5) t2 on t2.id=t1.id+1 order by t1.id ascrow_id -------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 CHARSET=UTF-8:茵-东莞 X-VOICE:13713026*** 2 CHARSET=UTF-8:变生 X-VOICE:13556776***(2 行受影响)
GO
CREATE TABLE TB(
ID INT
,VAL VARCHAR(100)
)
INSERT INTO TB
SELECT 1 ,'BEGIN:VCARD NULL' UNION ALL
SELECT 2 ,'VERSION:2.1 NULL' UNION ALL
SELECT 3 ,'N CHARSET=UTF-8:' UNION ALL
SELECT 4 ,'FN CHARSET=UTF-8:茵-东莞' UNION ALL
SELECT 5 ,'TEL X-VOICE:13713026***' UNION ALL
SELECT 6 ,'END:VCARD NULL' UNION ALL
SELECT 7 ,'BEGIN:VCARD NULL' UNION ALL
SELECT 8 ,'VERSION:2.1 NULL' UNION ALL
SELECT 9 ,'N CHARSET=UTF-8:' UNION ALL
SELECT 10 ,'FN CHARSET=UTF-8:变生' UNION ALL
SELECT 11 ,'TEL X-VOICE:13556776***' UNION ALL
SELECT 12 ,'END:VCARD NULL'
;WITH MU AS (
SELECT *
,(SELECT COUNT(1) FROM TB T2 WHERE T2.VAL LIKE 'BEGIN%' AND T2.ID<=T1.ID) AS NID
FROM TB T1
)
SELECT
LTRIM(NID)+':'
+MAX(CASE WHEN VAL LIKE 'N %' THEN STUFF(VAL,1,2,'') ELSE NULL END)
+MAX(CASE WHEN VAL LIKE 'FN %' THEN STUFF(VAL,1,CHARINDEX(':',VAL),'') ELSE NULL END)
+MAX(CASE WHEN VAL LIKE 'TEL %' THEN STUFF(VAL,1,3,'') ELSE NULL END)
FROM MU
GROUP BY NID
/*
1:CHARSET=UTF-8:茵-东莞 X-VOICE:13713026***
2:CHARSET=UTF-8:变生 X-VOICE:13556776***
*/
<BEGIN:VCARD> NULL
<VERSION:2.1> NULL
<N CHARSET=UTF-8:>
<FN CHARSET=UTF-8:>茵-东莞</>
<TEL X-VOICE:>13713026***</>
</END:VCARD> NULL
可以使用XML的写法。
规律是每六行有一组数据!
要得到的数据是,第4,5行的数据!
ID INT
,VAL VARCHAR(100)
)
INSERT INTO #TB
SELECT 1 ,'BEGIN:VCARD NULL' UNION ALL
SELECT 2 ,'VERSION:2.1 NULL' UNION ALL
SELECT 3 ,'N CHARSET=UTF-8:' UNION ALL
SELECT 4 ,'FN CHARSET=UTF-8:茵-东莞' UNION ALL
SELECT 5 ,'TEL X-VOICE:13713026***' UNION ALL
SELECT 6 ,'END:VCARD NULL' UNION ALL
SELECT 7 ,'BEGIN:VCARD NULL' UNION ALL
SELECT 8 ,'VERSION:2.1 NULL' UNION ALL
SELECT 9 ,'N CHARSET=UTF-8:' UNION ALL
SELECT 10 ,'FN CHARSET=UTF-8:变生' UNION ALL
SELECT 11 ,'TEL X-VOICE:13556776***' UNION ALL
SELECT 12 ,'END:VCARD NULL'select row_id=row_number() over(order by t1.id asc),t1.name+' '+t2.name
from
(select id,val,name=stuff(val,1,3,'') from #tb where id%6=4) t1
inner join
(select id,val,name=stuff(val,1,4,'') from #tb where id%6=5) t2
on t2.id=t1.id+1
order by t1.id ascrow_id
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 CHARSET=UTF-8:茵-东莞 X-VOICE:13713026***
2 CHARSET=UTF-8:变生 X-VOICE:13556776***(2 行受影响)