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

Can't you fix the data when you import it, so it goes into multiple columns? — Gordon Linoff, Jul 31 '19 at 16:49
Is it possible to fix the design and put these values in separate columns? That is the best way of dealing with this. — Sean Lange, Jul 31 '19 at 16:50
What version of SQL? If 2017 you can just use split string: https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-2017 Or create your own version of the function. https://stackoverflow.com/questions/6619810/how-to-split-strings-in-sql-server/6619841 — Brad, Jul 31 '19 at 16:50
There are tons of split-string functions that return element numbers that allow you to cross apply against the string column, and then select just the 3rd item, for example. — pmbAustin, Jul 31 '19 at 16:57

score 1 · Accepted Answer · answered Jul 31 '19 at 16:52

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

answered Jul 31 '19 at 16:52

John Cappelletti

79,615
7
44
66

@user_KoBy Happy to help – John Cappelletti Jul 31 '19 at 17:08

Ilyes · Answer 2 · 2019-07-31T17:21:31.167

1

You can also use STRING_SPLIT() if you have 2016+

CREATE TABLE T(
  ID INT IDENTITY(1,1),
  Str VARCHAR(45)
);

INSERT INTO T(Str) VALUES 
('Asset|Class1|Category1|Group1|Account1'),
('Expense|Class23|Category23|Group23|Account23');

SELECT V Str
FROM (
       SELECT Value V,
              ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) RN
       FROM T CROSS APPLY STRING_SPLIT(Str, '|')
     ) TT
WHERE RN = 3;

Returns:

Str
---------
Category1
Category23

edited Jul 31 '19 at 17:21

answered Jul 31 '19 at 17:14

Ilyes

14,640
4
29
55

FYI there is not GTD on the order of string_split(). https://dba.stackexchange.com/questions/207274/string-split-and-ordered-results Full disclosure, I've yet to see it fail, but then again, I've yet to be struck by a bolt of lightening. :) – John Cappelletti Jul 31 '19 at 17:25
It's not just having 2016+ but your Database has to be set to Compatibility level 130 as well. – DeFlanko Oct 19 '20 at 19:08

select string between 3rd and 4th pipe delimiter

2 Answers2