將複合字串拆成多欄位-以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)')
歡迎推文分享:
Published 09 March 2016 06:32 AM 由 Jeffrey
Filed under: ,
Views: 11,575



意見

# Jumo said on 11 March, 2016 03:37 AM

建立一個 function index 不知對您是否有幫助? 例如: CREATE INDEX IDX_BLAH ON BLAH (OrgId || '-' || DeptId || '-' || UserId )

# Jeffrey said on 11 March, 2016 05:02 AM

to Jumo, 原來Oracle有Function Index這種好東西,學習了。謝謝分享!已加入本文。

# allen said on 07 June, 2016 11:54 PM

您好:

小弟有一個類似但是不完全相同的狀況不知道是不是有解

也就是前端傳入的參數是變動的數量組合

比如status有1-7,前端用checkbox選項,組成類似

@status ='1-2-5-6-7'...之類的組合

每種狀態有不同的後續判斷式處理(我也不曉得前端為什麼要用組合式的...)

因為拆出來的變數數量不一定,不知有無方式處理...

# Jeffrey said on 08 June, 2016 12:28 AM

to allen, 看不太懂,恐怕你得提供輸入內容長什麼樣子,要拆成什麼樣子,用什麼WHERE條件查詢,變成具體一點的題目(要到印成考卷,考生不用舉手發問就能作答的詳細度),大家才有辦法挑戰。

# 世賢 said on 11 October, 2016 03:01 AM

allen想要問的應該就類似問卷調查複選的統計結果吧

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<March 2016>
SunMonTueWedThuFriSat
282912345
6789101112
13141516171819
20212223242526
272829303112
3456789
 
RSS
創用 CC 授權條款
【廣告】
twMVC
最新回應

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication