解析字串後 join

朋友的需求…

不計效能以及其他的優劣…

 

USE tempdb
GO
CREATE TABLE t1(c1 int identity primary key,c2 varchar(100))
INSERT t1 VALUES(‘1’),(‘2|3’),(‘4|’),(‘|5’),(NULL)
CREATE TABLE t2(c1 int identity primary key, val nvarchar(50))
INSERT t2 VALUES(N’甲’),(N’乙’),(N’丙’),(N’丁’),(N’戊’)
GO

 

CREATE FUNCTION iot_fn_A_Parsing
(
    @str varchar(1000)
)
RETURNS
@tbl TABLE
(
    ItemID INT
)
AS
BEGIN
    DECLARE @intIdx INT=0
    SET @intIdx=CharIndex(‘|’,@str,1)
    WHILE @intIdx>0
    BEGIN
        INSERT @tbl VALUES(CONVERT(int,LEFT(@str,@intIdx-1)))
        SET @str=RIGHT(@str,Len(@str)-@intIdx)
        SET @intIdx=CharIndex(‘|’,@str,1)
    END   
    INSERT @tbl VALUES(CONVERT(int,@str))
    RETURN
END
GO

 

SELECT * FROM t1 CROSS APPLY dbo.iot_fn_A_Parsing(c2) fn
JOIN t2 ON fn.ItemID=t2.c1

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: