Koopa_Coffee Koopa_Coffee - 2 years ago 124
SQL Question

A SQL Query to select a string between two known strings

I need a SQL query to get the value between two known strings (the returned value should start and end with these two strings).

An example.

"All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought."

In this case the known strings are "the dog" and "immediately". So my query should return "the dog had been very bad and required harsh punishment immediately"

I've come up with this so far but to no avail:

SELECT SUBSTRING(@Text, CHARINDEX('the dog', @Text), CHARINDEX('immediately', @Text))

@Text being the variable containing the main string.

Can someone please help me with where I'm going wrong?

Answer Source

The problem is that the second part of your substring argument is including the first index. You need to subtract the first index from your second index to make this work.

, CHARINDEX('immediately',@text) - CHARINDEX('the dog', @Text) + Len('immediately'))
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download