----------------------------------------------------------------
-- 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
*/
-- 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,charindex(@StrSeprate,@SourceSql,1)-1)
insert @temp values(@ch)
set @SourceSql=stuff(@SourceSql,1,charindex(@StrSeprate,@SourceSql,1),'')
end
return
end
--生成示例资料
IF EXISTS(SELECT NAME FROM sys.objects WHERE NAME = 'test')
DROP TABLE test
GO
CREATE TABLE test(keycode VARCHAR(10), username VARCHAR(50))
GO
INSERT INTO test
SELECT '2014' , '|u1|u2|u3|' UNION ALL
SELECT '2015' , '|u4|u5|u6|'
GO
--执行 SELECT b.f1 , test.* FROM test cross apply f_splitstr(SUBSTRING(test.username,2,LEN(test.username) - 2) , '|') AS b
/*执行结果
f1 keycode username
-------- ---------- --------------------------------------------------
u1 2014 |u1|u2|u3|
u2 2014 |u1|u2|u3|
u3 2014 |u1|u2|u3|
u4 2015 |u4|u5|u6|
u5 2015 |u4|u5|u6|
u6 2015 |u4|u5|u6|(6 row(s) affected)
*/
这句只有错误,是怎么回事呢
'(' 附近有语法错误。
这句只有错误,是怎么回事呢
'(' 附近有语法错误。
你的版本是多少?cross apply要SQL SERVER 2005以上