Solved

Query to select a string between two known strings

Posted on 2013-01-09
9
3,296 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 142

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 142

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Slow Connectivity over ODBC 8 34
SqlServer no dupes 25 34
Problem when I run a simple storeproc - help 4 16
How to use three values with DATEDIFF 3 20
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

770 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