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
– HABO, Commented Jul 23, 2020 at 19:26
Does this answer your question? A SQL Query to select a string between two known strings — Eric Brandt
– Eric Brandt, Commented Jul 23, 2020 at 20:13

Lars Skaug · Accepted Answer · 2020-07-23 20:02:59Z

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, 2020 at 20:02

answered Jul 23, 2020 at 19:53

Lars Skaug

1,3861 gold badge9 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rajat · Accepted Answer · 2020-07-23 22:10:44Z

1

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 -----%';

answered Jul 23, 2020 at 22:10

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Collectives™ on Stack Overflow

T-SQL Extract string between two known strings

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related