?
Solved

Query to select a string between two known strings

Posted on 2013-01-09
9
Medium Priority
?
4,668 Views
Last Modified: 2013-01-10
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
Comment
Question by:miller75
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 15

Expert Comment

by:gplana
ID: 38761934
Just use WHERE and BETWEEN operator:

SELECT *
FROM your_table
WHERE field BETWEEN string1 AND string2;
0
 

Author Comment

by:miller75
ID: 38761954
Just tried

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


and it just selects the entire field
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38761984
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 15

Expert Comment

by:gplana
ID: 38761985
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
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38762005
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
 

Author Comment

by:miller75
ID: 38762050
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
 
LVL 15

Expert Comment

by:gplana
ID: 38762077
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 38762083
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
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 38762087
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

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question