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

ORACLE

-- :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

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)')

原始介紹文章出自於此