[sql] A SQL Query to select a string between two known strings

I need a SQL query to get the value between two known strings (the returned value should start and end with these two strings).

An example.

"All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought."

In this case the known strings are "the dog" and "immediately". So my query should return "the dog had been very bad and required harsh punishment immediately"

I've come up with this so far but to no avail:

SELECT SUBSTRING(@Text, CHARINDEX('the dog', @Text), CHARINDEX('immediately', @Text))

@Text being the variable containing the main string.

Can someone please help me with where I'm going wrong?

This question is related to sql sql-server substring

The answer is


I had a similar need to parse out a set of parameters stored within an IIS logs' csUriQuery field, which looked like this: id=3598308&user=AD\user&parameter=1&listing=No needed in this format.

I ended up creating a User-defined function to accomplish a string between, with the following assumptions:

  1. If the starting occurrence is not found, a NULL is returned, and
  2. If the ending occurrence is not found, the rest of the string is returned

Here's the code:

CREATE FUNCTION dbo.str_between(@col varchar(max), @start varchar(50), @end varchar(50))  
  RETURNS varchar(max)  
  WITH EXECUTE AS CALLER  
AS  
BEGIN  
  RETURN substring(@col, charindex(@start, @col) + len(@start), 
         isnull(nullif(charindex(@end, stuff(@col, 1, charindex(@start, @col)-1, '')),0),
         len(stuff(@col, 1, charindex(@start, @col)-1, ''))+1) - len(@start)-1);
END;  
GO

For the above question, the usage is as follows:

DECLARE @a VARCHAR(MAX) = 'All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought.'
SELECT dbo.str_between(@a, 'the dog', 'immediately')
-- Yields' had been very bad and required harsh punishment '

I think what Evan meant was this:

SELECT SUBSTRING(@Text, CHARINDEX(@First, @Text) + LEN(@First), 
                 CHARINDEX(@Second, @Text) - CHARINDEX(@First, @Text) - LEN(@First))

SELECT SUBSTRING('aaaaa$bbbbb$ccccc',instr('aaaaa$bbbbb$ccccc','$',1,1)+1, instr('aaaaa$bbbbb$ccccc','$',1,2)-1) -instr('aaaaa$bbbbb$ccccc','$',1,1)) as My_String

Hope this helps : Declared a variable , in case of any changes need to be made thats only once .

declare @line  varchar(100)

set @line ='[email protected]'

select SUBSTRING(@line ,(charindex('-',@line)+1), CHARINDEX('@',@line)-charindex('-',@line)-1)

SELECT 
SUBSTRING( '[email protected]',  charindex('@','[email protected]',1) + 1, charindex('.','[email protected]',1) - charindex('@','[email protected]',1) - 1 )

<pre>

DECLARE @text VARCHAR(MAX)

SET @text = 'All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought.'

declare @pretext as nvarchar(100) = 'the dog'    
declare @posttext as nvarchar(100) = 'immediately'

SELECT 
 CASE 
When CHARINDEX(@posttext, @Text) - (CHARINDEX(@pretext, @Text) + len(@pretext)) < 0 THEN 
''
Else
SUBSTRING(@Text, CHARINDEX(@pretext, @Text) + len(@pretext)
,   CHARINDEX(@posttext, @Text) - (CHARINDEX(@pretext, @Text) + len(@pretext)) )    
END as betweentext  


You're getting the starting position of 'punishment immediately', but passing that in as the length parameter for your substring.

You would need to substract the starting position of 'the dog' from the charindex of 'punishment immediately', and then add the length of the 'punishment immediately' string to your third parameter. This would then give you the correct text.

Here's some rough, hacky code to illustrate the process:

DECLARE @text VARCHAR(MAX)
SET @text = 'All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought.'

DECLARE @start INT
SELECT @start = CHARINDEX('the dog',@text)

DECLARE @endLen INT
SELECT @endLen = LEN('immediately')

DECLARE @end INT
SELECT @end = CHARINDEX('immediately',@text)
SET @end = @end - @start + @endLen

SELECT @end

SELECT SUBSTRING(@text,@start,@end)

Result: the dog had been very bad and required harsh punishment immediately


I have a feeling you might need SQL Server's PATINDEX() function. Check this out:

Usage on Patindex() function

So maybe:

SELECT SUBSTRING(@TEXT, PATINDEX('%the dog%', @TEXT), PATINDEX('%immediately%',@TEXT))

You need to adjust for the LENGTH in the SUBSTRING. You were pointing it to the END of the 'ending string'.

Try something like this:

declare @TEXT varchar(200)
declare @ST varchar(200)
declare @EN varchar(200)
set @ST = 'the dog'
set @EN = 'immediately'
set @TEXT = 'All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought.'
SELECT SUBSTRING(@Text, CHARINDEX(@ST, @Text), (CHARINDEX(@EN, @Text)+LEN(@EN))-CHARINDEX(@ST, @Text))

Of course, you may need to adjust it a bit.


select substring(@string,charindex('@first',@string)+1,charindex('@second',@string)-(charindex('@first',@string)+1))

DECLARE @Text VARCHAR(MAX), @First VARCHAR(MAX), @Second VARCHAR(MAX)
SET @Text = 'All I knew was that the dog had been very bad and required harsh punishment immediately regardless of what anyone else thought.'
SET @First = 'the dog'
SET @Second = 'immediately'

SELECT SUBSTRING(@Text, CHARINDEX(@First, @Text), 
                 CHARINDEX(@Second, @Text) - CHARINDEX(@First, @Text) + LEN(@Second))

An example is this: You have a string and the character $

String :

aaaaa$bbbbb$ccccc

Code:

SELECT SUBSTRING('aaaaa$bbbbb$ccccc',CHARINDEX('$','aaaaa$bbbbb$ccccc')+1, CHARINDEX('$','aaaaa$bbbbb$ccccc',CHARINDEX('$','aaaaa$bbbbb$ccccc')+1) -CHARINDEX('$','aaaaa$bbbbb$ccccc')-1) as My_String

Output:

bbbbb

Try this and replace '[' & ']' with your string

SELECT SUBSTRING(@TEXT,CHARINDEX('[',@TEXT)+1,(CHARINDEX(']',@TEXT)-CHARINDEX('[',@TEXT))-1)

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to sql-server

Passing multiple values for same variable in stored procedure SQL permissions for roles Count the Number of Tables in a SQL Server Database Visual Studio 2017 does not have Business Intelligence Integration Services/Projects ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database How to create temp table using Create statement in SQL Server? SQL Query Where Date = Today Minus 7 Days How do I pass a list as a parameter in a stored procedure? SQL Server date format yyyymmdd

Examples related to substring

Go test string contains substring How does String substring work in Swift Delete the last two characters of the String Split String by delimiter position using oracle SQL How do I check if a string contains another string in Swift? Python: Find a substring in a string and returning the index of the substring bash, extract string before a colon SQL SELECT everything after a certain character Finding second occurrence of a substring in a string in Java Select query to remove non-numeric characters