Solved

Query to select a string between two known strings

Posted on 2013-01-09
9
3,505 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 26

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 500 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 26

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

837 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