日期:2014-05-16 浏览次数:20589 次
----------------------------------------------------------------
-- Author :谭磊
-- Date :2014-04-29 11:51:04
-- Version:
-- Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
-- Jun 28 2012 08:36:30
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据:#TA
IF OBJECT_ID('tempdb.dbo.#TA') IS NOT NULL
DROP TABLE #TA
GO
CREATE TABLE #TA
(
[keycode] INT ,
[username] VARCHAR(10)
)
INSERT #TA
SELECT 2014, '
------解决方案--------------------
u1
------解决方案--------------------
u2
------解决方案--------------------
u3
------解决方案--------------------
'
UNION ALL
SELECT 2015, '
------解决方案--------------------
u4
------解决方案--------------------
u5
------解决方案--------------------
u6
------解决方案--------------------
'
--------------开始查询--------------------------
SELECT a.keycode, SUBSTRING(a.username, n.number, CHARINDEX('
------解决方案--------------------
', a.username + '
------解决方案--------------------
', n.number) - n.number) AS element
FROM #Ta a
JOIN master..spt_values n
ON n.number <= LEN(username)
AND n.type = 'p'
AND SUBSTRING('
------解决方案--------------------
' + a.username, n.number, 1) = '
------解决方案--------------------
'
AND SUBSTRING(a.username, n.number, CHARINDEX('
------解决方案--------------------
', a.username + '
------解决方案--------------------
', n.number) - n.number) <> ''
----------------结果----------------------------
/*
2014 u1
2014 u2
2014 u3
2015 u4
2015 u5
2015 u6
*/
--创建函数
create???function???f_splitstr(@SourceSql???varchar(8000),@StrSeprate???varchar(100))???
??returns???@temp???table(F1???varchar(100))???
??as?????
??begin???
??declare???@ch???as???varchar(100)???
??set???@SourceSql=@SourceSql+@StrSeprate?????
??while(@SourceSql<>'')???
??????????????????begin???
??????????????????set???@ch=left(@SourceSql,charinde