Parse text in the middle of a string - SQL Server

Question

I have hundreds of rows in a string that look like this:

filler|filler|scrape this text|and this text|sometimes this to|filler|filler

Is it possible to select only the text after the 2nd | and before the last 2 |s?

Recommend reading: [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) — GMB, Dec 31 '19 at 15:16
What is your expected output and why? What is the meaning of - last 2 |s? — Suraj Kumar, Dec 31 '19 at 15:20
I would expect the output to be scrape this text|and this text|sometimes this to — AAA, Dec 31 '19 at 15:23
I would suggest looking up a splitter that provides ordinal position, and omitting the "top" and "bottom" 2, and then rebuilding the string. But not storing your data in a delimited format is the *real* solution here; and why you're having the problem you are. — Thom A, Dec 31 '19 at 15:30

score 2 · Accepted Answer · answered Dec 31 '19 at 15:30

Since you are on 2016, string_split() would be an option, but there is no GTD of the order. That said, consider a little XML

Example

Declare @YourTable Table ([ID] int,[SomeCol] varchar(150))
Insert Into @YourTable Values 
(1,'filler|filler|scrape this text|and this text|sometimes this to|filler|filler')

Select A.ID 
      ,B.Pos3
      ,B.Pos4 
      ,B.Pos5
 From @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                      ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
                      ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
                      ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
                From  ( values (cast('<x>' + replace((Select replace(SomeCol,'|','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml)))  A(xDim)
             ) B

Returns

ID  Pos3                Pos4            Pos5
1   scrape this text    and this text   sometimes this to

score 1 · Answer 2 · answered Dec 31 '19 at 16:48

JSON-based approach is also an option here. You need to transform the input text into a valid JSON array and get each array item with JSON_VALUE() by index (0-based).

Table:

CREATE TABLE Data (
   TextData nvarchar(max)
)
INSERT INTO Data (TextData)
VALUES (N'filler|filler|scrape this text|and this text|sometimes this to|filler|filler')

Statement:

SELECT 
   JSON_VALUE(CONCAT(N'["', REPLACE(d.TextData, N'|', N'","'), N'"]'), '$[2]') AS Text3,
   JSON_VALUE(CONCAT(N'["', REPLACE(d.TextData, N'|', N'","'), N'"]'), '$[3]') AS Text4,
   JSON_VALUE(CONCAT(N'["', REPLACE(d.TextData, N'|', N'","'), N'"]'), '$[4]') AS Text5
FROM Data d

Result:

-----------------------------------------------------
Text3               Text4           Text5
-----------------------------------------------------
scrape this text    and this text   sometimes this to

Parse text in the middle of a string - SQL Server

2 Answers2