I was just going to mention that. Here's a revised version to include strings with no matches at all using OUTER APPLY.

WITH cteString ( ArtifactID, PATH )

AS ( SELECT ArtifactID ,

PATH

FROM [Document]

WHERE PATH IS NOT NULL

)

SELECT ArtifactID ,

PATH ,

y.pattern ,

ISNULL( y.Datatype, 'Check Subject and Record type etc.') AS Datatype

FROM cteString

OUTER APPLY ( SELECT TOP 1

pattern, Datatype

FROM ( VALUES

( 'PublicIM', '_pim', CHARINDEX('_pim', PATH)),

( 'HubIMs', '_him', CHARINDEX('_him', PATH)),

( 'B Email', '.b.', CHARINDEX('.b.', PATH)),

( 'B Chats', '.ib', CHARINDEX('.ib', PATH)),

( 'Bloomberg Chats', 'IB Conversation', CHARINDEX('IB Conversation',

path)) ) x (Datatype, pattern, position )

WHERE position > 0

ORDER BY position

) y

EDIT: Oracle? :crazy::sick: