T-SQL Extract string between two known strings

Question

I have the following Text:

"Original ----- The cow jumped over the moon ----- 20200723 --NEW-- The cow jumped over the sun ----- "

I'm trying to write a t-sql query that will extract everything between the string "Original ----- " and the following " ----- ", so my outcome would be:

"Original ----- The cow jumped over the moon ----- "

I've tried writing something like this:

declare @Text nvarchar(max) = 'Original ----- The cow jumped over the moon ----- 20200723 --NEW-- The cow jumped over the sun ----- '
select SUBSTRING(@Text, CHARINDEX('Original ----- ', @Text)
, CHARINDEX(' ----- ',@Text) - CHARINDEX('Original ----- ', @Text) + Len(' ----- '))

But it just returns Original -----. Please help!

`CHARINDEX(' ----- ',@Text) - CHARINDEX('Original ----- ', @Text)` finds the same occurrence of `' ----- '` twice. — HABO, Jul 23 '20 at 19:26
Does this answer your question? [A SQL Query to select a string between two known strings](https://stackoverflow.com/questions/18362260/a-sql-query-to-select-a-string-between-two-known-strings) — Eric Brandt, Jul 23 '20 at 20:13

Lars Skaug · Accepted Answer · 2020-07-23T20:02:59.537

1

CHARINDEX has a third, optional argument, which is the start. Modify your query as follows to begin looking for --- after the first occurence.

select SUBSTRING(@Text, CHARINDEX('Original ----- ', @Text)
, CHARINDEX(' ----- ',@Text, CHARINDEX('Original ----- ', @Text) + len('Original ----- '))) + '-----';

A quick Fiddle to demonstrate.

edited Jul 23 '20 at 20:02

answered Jul 23 '20 at 19:53

Lars Skaug

1,376
1
7
13

score 1 · Answer 2 · answered Jul 23 '20 at 22:10

You could rely on the fact that Original ----- is a fixed number of characters and hard code that value into your code. Then either use a where clause or a case expression to only apply it to those starting with Original -----

select 'Original ----- '+substring(col,16,charindex( '-----',col, 16)-11)
from t
where col like 'Original -----%';

T-SQL Extract string between two known strings

2 Answers2