select string between 3rd and 4th pipe delimiter

Question

I have a column that contains values such as

Column
Asset|Class1|Category1|Group1|Account1
Expense|Class23|Category23|Group23|Account23

I want to select the string between 3rd and 4th occurrence of my pipe delimiter, how can I achieve this?

I've tried the PARSENAME and charindex+stuff function, but they have limitations, like max 128 characters. Also our SQL server has limited regex support. Any ideas?

SELECT REVERSE(PARSENAME(REVERSE(replace(LTRIM(Column), '|', '.')), 3))

My select need to return: Group1 Group23

Answer 1

Perhaps this will help

Example

Declare @YourTable table (ID int,[Column] varchar(max))
Insert Into @YourTable values
 (1,'Asset|Class1|Category1|Group1|Account1')
,(2,'Expense|Class23|Category23|Group23|Account23')

Select ID
      ,SomeValue = convert(xml,'<x>' + replace([Column],'|','</x><x>')+'</x>').value('/x[3]','varchar(100)')
 From @YourTable

Returns

ID  SomeValue
1   Category1
2   Category23