將複合字串拆成多欄位－以ORACLE及SQL為例

先說說我的需求。某資料表使用複合欄位當Primary Key，例如：由OrgId、DeptId、UserId三欄組成唯一鍵值。當要查詢特定資料，理論上應寫成WHERE OrgId='…' AND DeptId = '…' AND UserId = '…'。為求簡便，在.NET程式端以及某些資料表我發明了一種複合代碼字串"OrgId-DeptId-UserId"，只用一個參數或一個欄位就搞定關聯，省時又省力。不過，每個做法總有黑暗面，當複合代碼字串要拿來查資料時，得先拆解成OrgId、DeptId、UserId三個值，在C#裡用個.Split('-')可以輕鬆搞定，但如果要在SQL裡處理就麻纇多了。

有一種很鳥但絕對可行的解法：

SELECT * FROM BLAH WHERE OrgId + '-' + DeptId + '-' + UserId = 'OO-XX-orz'

保證你能正確查到資料，但你知道我知道獨眼龍也知道，遇到欄位加工再比對的寫法，DB必須逐筆處理無法靠索引加速，速度慢又不環保。
2016-03-11 補充，Oracle有Function-Based Index，欄位加工再比對也可使用Index。感謝Jumo補充。

最近在ORACLE再次與此問題狹路相逢，這回爬文學到一個神奇函數－REGEXP_SUBSTR，可在ORACLE用Regular Expression處理字串，做到類似Split()的效果。透過REGEXP_SUBSTR('FBI-IT01-12345', '[^-]', 1, 2)，用'-'符號可將字串拆成三段，最後一個參數2可指定要取出第幾段。於是，老問題找到新解法了：

-- :pFindKey = 'FBI-IT01-12345'

--方法1

select * from BLAH where OrgId || '-' || DeptId || '-' || UserId = :pFindKey

--方法2

select * from BLAH where 

       OrgtId = REGEXP_SUBSTR(:pFindKey, '[^-]+', 1, 1) and

       DeptId = REGEXP_SUBSTR(:pFindKey, '[^-]+', 1, 2) and

       UserId = REGEXP_SUBSTR(:pFIndKey, '[^-]+', 1, 3)

那那那那，在SQL Server遇到這個問題怎麼辦？SQL沒提供支援Regular Expression的內建字串函數，這類官方多半建議用威力強大的SQLCLR解決，但實務上一旦引用自訂Stored Procedure、User Defined Function會增加部署的需求，在我眼中，完全靠內建函式搞定才是王道呀！

找到一記妙招，將'FBI-IT01-12345'轉成'<n>FBI</n><n>IT01</n><n>12345</n>'後轉型成SQLXML型別，就可用.value('(/n)[2]', 'varchar(16)')取出'IT01'，如此在SQL也能將複合字串拆成多欄位囉！

declare @pFindKey varchar(32) 

set @pFindKey = 'FBI-IT1-12345'

--方法1

select * from BLAH where OrgId + '-' + DeptId + '-' + UserId = @pFindKey

--方法2

select BLAH.* from BLAH

join (select convert(xml, '<n>' + replace(@pFindKey, '-','</n><n>') + '</n>') as x) Keys

on OrgId = Keys.x.value('(/n)[1]', 'varchar(16)') and 

   DeptId = Keys.x.value('(/n)[2]', 'varchar(16)') and 

   UserId = Keys.x.value('(/n)[3]', 'varchar(16)')