Query to select a string between two known strings

Just use WHERE and BETWEEN operator:

SELECT *
FROM your_table
WHERE field BETWEEN string1 AND string2;

Just tried

SELECT [fld1]
FROM [WebAdmin].[dbo].[BCPData]
WHERE [fld1] BETWEEN 'aaaaaa' AND 'zzzzzz'


and it just selects the entire field

I think you are looking for this kind of expression:

declare @text varchar(1000)
set @text ='123 abc this text def 234'

declare @start varchar(100)
set @start = 'abc'

declare @end varchar(100)
set @end = 'def'

select @text text_to_search
, substring( LEFT(@text, charindex(@end, @text)-1), CHARINDEX(@start, @text) + len(@start), LEN(@text))   result

Select allOpen in new window

Oh, I think I haven't understood your question before. I believed you want to select fields whose value is between two strings.

Ok, if you want to get a string part with a pre and a post try this:

SELECT REPLACE(REPLACE([fld1],'aaaaaaaa',''),'bbbbb','')
FROM [WebAdmin].[dbo].[BCPData]
WHERE PATINDEX('aaaaaaa%bbbbbb',[fld1]);

Select allOpen in new window

You can try something like this to pull out the text

DECLARE @targetString VARCHAR(500) = 'even if something else in front of it aaaaaa this text has to be selected zzzzzz and more stuff following';
DECLARE @knownStartString VARCHAR(100) = 'aaaaaa';
DECLARE @knownEndString VARCHAR(100) = 'zzzzzz';
DECLARE @expectedOutputString VARCHAR(100) = 'this text has to be selected';

SELECT SUBSTRING(@targetString, CHARINDEX(@knownStartString, @targetString) + LEN(@knownStartString) + 1, CHARINDEX(@knownEndString,@targetString) - (CHARINDEX(@knownStartString, @targetString) + LEN(@knownStartString)+2))

Select allOpen in new window

I have run into an unforeseen issue, the end sting could appear multiple times including before the start string
eg
zzzzzz some other text aaaaaa this text has to be selected zzzzzz

Is there a way to set the starting search point for the end string at the start string position (If that makes sense)

then change CGLutrell solutio nby:

DECLARE @targetString VARCHAR(500) = 'zzzzzz even if something else in front of it aaaaaa this text has to be selected zzzzzz and more stuff following';
DECLARE @knownStartString VARCHAR(100) = 'aaaaaa';
DECLARE @knownEndString VARCHAR(100) = 'zzzzzz';
DECLARE @expectedOutputString VARCHAR(100) = 'this text has to be selected';

SELECT SUBSTRING(@targetString, PATINDEX('%' + @knownStartString+'%'+@knownEndString, @targetString) + LEN(@knownStartString) + 1, CHARINDEX(@knownEndString,@targetString) -  PATINDEX('%' + @knownStartString+'%'+@knownEndString, @targetString) + LEN(@knownStartString) + 1 + LEN(@knownStartString)+2))

Yes, like this

DECLARE @targetString VARCHAR(500) = 'even if something else including zzzzzz is in front of it aaaaaa this text has to be selected zzzzzz and more stuff following';
DECLARE @knownStartString VARCHAR(100) = 'aaaaaa';
DECLARE @knownEndString VARCHAR(100) = 'zzzzzz';
DECLARE @expectedOutputString VARCHAR(100) = 'this text has to be selected';

SELECT SUBSTRING(@targetString, CHARINDEX(@knownStartString, @targetString) + LEN(@knownStartString) + 1, CHARINDEX(@knownEndString,@targetString, CHARINDEX(@knownStartString, @targetString)) - (CHARINDEX(@knownStartString, @targetString) + LEN(@knownStartString)+2))

Select allOpen in new window