[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5730
  • Last Modified:

Query to select a string between two known strings

I want to select a string between two known strings, the length of the sting to be selected isn't constant and the position of the qualifying strings isn't fixed either

eg
aaaaaa this text has to be selected zzzzzz

Any help would be appreciated
0
miller75
Asked:
miller75
  • 3
  • 2
  • 2
  • +1
1 Solution
 
gplanaCommented:
Just use WHERE and BETWEEN operator:

SELECT *
FROM your_table
WHERE field BETWEEN string1 AND string2;
0
 
miller75Author Commented:
Just tried

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


and it just selects the entire field
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gplanaCommented:
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]);

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
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))

Open in new window

0
 
miller75Author Commented:
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)
0
 
gplanaCommented:
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))
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
a small modification will do:
declare @text varchar(1000)
set @text ='def 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, CHARINDEX(@start, @text))-1), CHARINDEX(@start, @text) + len(@start), LEN(@text))   result

Open in new window


there is a 3rd parameter to charindex to indicate the starting position to search from.
0
 
Chris LuttrellSenior Database ArchitectCommented:
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))

Open in new window

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now