---------------------------------------
-- 將資料庫的字串分解後,直接轉換成欄位
---------------------------------------
/*
mystring
------------------------
AAA_BBBB_CC
DDD_EEEE_FF
--結果
1 2 3
AAA BBBB CC
DDD EEEE FF
*/
---解法,利用傳統函數將字串分解,並且搭配NEWID讓每一組字串有獨立代號
CREATE FUNCTION [dbo].[fnSplitString]
( @uid uniqueidentifier,
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(uid uniqueidentifier,id int identity,splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1
INSERT INTO @output (uid,splitdata)
VALUES(@uid,SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
delete from @output where splitdata=''
END
RETURN
END
GO
-- 驗證
select * from [dbo].[fnSplitString](newid(),'AAA_BBBB_CC','_')
uid id splitdata
3C6334E4-09B4-48C9-853F-B1B9056B016D 1 AAA
3C6334E4-09B4-48C9-853F-B1B9056B016D 2 BBBB
3C6334E4-09B4-48C9-853F-B1B9056B016D 3 CC
-- 測試資料表
create table tblString
(mystring varchar(1024))
go
--新增驗證資料
insert into tblString(mystring) values('AAA_BBBB_CC'),('DDD_EEEE_FF');
--查詢資料
select * from tblString
go
--結果
mystring
AAA_BBBB_CC
DDD_EEEE_FF
--使用函數分解
SELECT uid,id,splitdata
FROM tblString cross apply [dbo].[fnSplitString](newid(),mystring,'_')
--結果
uid id splitdata
D4A2885D-16FD-4F6E-934E-7E945EB2B236 1 AAA
D4A2885D-16FD-4F6E-934E-7E945EB2B236 2 BBBB
D4A2885D-16FD-4F6E-934E-7E945EB2B236 3 CC
18EA0508-634F-4235-90D5-2FC85139047D 1 DDD
18EA0508-634F-4235-90D5-2FC85139047D 2 EEEE
18EA0508-634F-4235-90D5-2FC85139047D 3 FF
--使用樞紐
SELECT t.[1],t.[2],t.[3]
FROM (SELECT uid,id,splitdata FROM tblString cross apply [dbo].[fnSplitString](newid(),mystring,'_')) as SftSales
PIVOT( MAX(splitdata) --使用彙總函數
FOR id IN ([1],[2],[3]) ) t --資料集別名
--結果
1 2 3
AAA BBBB CC
DDD EEEE FF
Last active
June 29, 2016 05:56
-
-
Save sujunmin/9853513572fea37ae7993e40ad3ae2ae to your computer and use it in GitHub Desktop.
SQL Server TSQL 開發技巧- 將資料庫的字串分解後_直接轉換成欄位 - PIVOT 與 SPLIT
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment