A SQL Query to select a string between two known strings

Question

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?

"the dog had been very bad and required harsh punishment immediately regardles" — coopertkm, Aug 21 '13 at 16:02

orgtigger · Accepted Answer · 2013-08-21T16:39:50.647

67

The problem is that the second part of your substring argument is including the first index. You need to subtract the first index from your second index to make this work.

SELECT SUBSTRING(@Text, CHARINDEX('the dog', @Text)
, CHARINDEX('immediately',@text) - CHARINDEX('the dog', @Text) + Len('immediately'))

edited Aug 21 '13 at 16:39

answered Aug 21 '13 at 16:33

orgtigger

3,914
1
20
22

12

This works if the bookends you're looking for only appear once each, but not if they appear more than once. – samthebrand Mar 24 '16 at 19:09

score 26 · Answer 2 · answered Aug 21 '13 at 16:36

26

I think what Evan meant was this:

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

answered Aug 21 '13 at 16:36

Data Masseur

1,163
7
15

4

Not exactly what the OP asked: this answer strips the strings on the edge. But this is a useful snippet. – samthebrand May 24 '17 at 18:10

score 22 · Answer 3 · edited May 11 '16 at 11:30

22

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

edited May 11 '16 at 11:30

gofr1

15,741
11
42
52

answered May 11 '16 at 11:12

Mark Roll

506
3
6
15

What if u have something like `'aaaaa$bbbbb$cc$ccc$'` and you want it to return 2 rows containing `bbbbb & ccc`? – John Pietrar Jul 06 '16 at 10:24
1

For the 1st row the select i provided is ok. For the 2nd one (aaaaa$bbbbb$cc$ccc) : select reverse(left(reverse('aaaaa$bbbbb$cc$ccc'), charindex('$', reverse('aaaaa$bbbbb$cc$ccc')) -1)) – Mark Roll Jul 06 '16 at 13:30
1

This doesn't really answer the question, as it assumes the "bookends" of the OPs string are the same character, which isn't true. – samthebrand Sep 29 '17 at 21:13

BWS · Answer 4 · 2013-08-21T16:27:50.183

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.

vGHazard · Answer 5 · 2018-02-06T21:29:28.287

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:

If the starting occurrence is not found, a NULL is returned, and
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 like this solution. It even works if you pass CHAR(xx) control characters like NEWLINE and CARRIAGE RETURN — Fandango68, Apr 20 '20 at 05:28

score 6 · Answer 6 · edited Mar 27 '15 at 13:51

6

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

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

edited Mar 27 '15 at 13:51

heldt

4,166
7
39
67

answered Mar 27 '15 at 13:21

agrawars

69
1
1

score 5 · Answer 7 · edited May 23 '17 at 12:09

5

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))

edited May 23 '17 at 12:09

Community

1
1

answered Aug 21 '13 at 16:08

eatonphil

13,115
27
76
133

score 5 · Answer 8 · edited May 16 '16 at 14:24

5

SELECT 
SUBSTRING( '123@yahoo.com',  charindex('@','123@yahoo.com',1) + 1, charindex('.','123@yahoo.com',1) - charindex('@','123@yahoo.com',1) - 1 )

edited May 16 '16 at 14:24

Rich Benner

7,873
9
33
39

answered May 16 '16 at 13:49

ganesh nandu raut

51
1
1

score 2 · Answer 9 · answered Aug 21 '13 at 16:26

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))

score 2 · Answer 10 · answered Aug 21 '13 at 16:39

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

EdWilson · Answer 11 · 2022-11-06T21:22:32.147

Among the many options is to create a simple function. Can keep your code cleaner. Gives the ability to handle errors if the start or end marker/string is not present. This function also allows for trimming leading or trailing whitespace as an option.

SELECT dbo.GetStringBetweenMarkers('123456789', '234', '78', 0, 1)

Yields:

56

--Code to create the function
USE [xxxx_YourDB_xxxx]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[GetStringBetweenMarkers] (@FullString varchar(max), @StartMarker varchar(500), @EndMarker varchar(500), @TrimLRWhiteSpace bit, @ReportErrorInResult bit)  
RETURNS varchar(max)
AS
BEGIN
    --Purpose is to simply return the string between 2 string markers. ew 2022-11-06
    --Will perform a LTRIM and RTRIM if @TrimLRWhiteSpace = 1
    --Will report errors of either marker not being found in the RETURNed string if @ReportErrorInResult = 1.  
    --      When @ReportErrorInResult = 0, if the start marker isn't found, will return everything from the start of the @FullString to the left of the end marker.
    --      When @ReportErrorInResult = 0, if the end marker isn't found, SQL will return an error of "Invalid length parameter passed to the LEFT or SUBSTRING function."
    DECLARE @ReturnString VARCHAR(max) = ''
    DECLARE @StartOfStartMarker INT = CHARINDEX(@StartMarker, @FullString)
    DECLARE @StartOfTarget INT = CHARINDEX(@StartMarker, @FullString) + LEN(@StartMarker)
    DECLARE @EndOfTarget INT = CHARINDEX(@EndMarker, @FullString, @StartOfTarget)

    --If a marker wasn't found, put that into the 
    IF @ReportErrorInResult = 1
    BEGIN
        IF @EndOfTarget = 0                           SET @ReturnString = '[ERROR: EndMarker not found.]'
        IF @StartOfStartMarker = 0                    SET @ReturnString = '[ERROR: StartMarker not found.]'
        IF @StartOfStartMarker = 0 AND @EndOfTarget = 0 SET @ReturnString = '[ERROR: Both StartMarker and EndMarker not found.]'
    END

    --If not reporting errors, and start marker not found (i.e. CHARINDEX = 0) we would start our string at the LEN(@StartMarker).  
    --    This would give an odd result.  Best to just provide from 0, i.e. the start of the @FullString.
    IF @ReportErrorInResult = 0 AND @StartOfStartMarker = 0 SET @StartOfTarget = 0

    --Main action
    IF @ReturnString = '' SET @ReturnString = SUBSTRING(@FullString, @StartOfTarget, @EndOfTarget - @StartOfTarget) 
    IF @TrimLRWhiteSpace = 1 SET @ReturnString = LTRIM(RTRIM(@ReturnString))
    RETURN @ReturnString

    --Examples
        --  SELECT '>' + dbo.GetStringBetweenMarkers('123456789','234','78',0,1) + '<' AS 'Result-Returns what is in between markers w/ white space'
        --  SELECT '>' + dbo.GetStringBetweenMarkers('1234  56  789','234','78',0,1) + '<' AS 'Result-Without trimming white space'
        --  SELECT '>' + dbo.GetStringBetweenMarkers('1234  56  789','234','78',1,1) + '<' AS 'Result-Will trim white space with a @TrimLRWhiteSpace = 1'
        --  SELECT '>' + dbo.GetStringBetweenMarkers('abcdefgh','ABC','FG',0,1) + '<' AS 'Result-Not Case Sensitive'
        --  SELECT '>' + dbo.GetStringBetweenMarkers('abc_de_fgh','_','_',0,1) + '<' AS 'Result-Using the same marker for start and end'
    --Errors are returned if start or end marker are not found    
        --  SELECT '>' + dbo.GetStringBetweenMarkers('1234 56789','zz','78',0,1) + '<' AS 'Result-Start not found'
        --  SELECT '>' + dbo.GetStringBetweenMarkers('1234 56789','234','zz',0,1) + '<' AS 'Result-End not found'
        --  SELECT '>' + dbo.GetStringBetweenMarkers('1234 56789','zz','zz',0,1) + '<' AS 'Result-Niether found'
    --If @ReportErrorInResult = 0
        --  SELECT '>' + dbo.GetStringBetweenMarkers('123456789','zz','78',0,0) + '<' AS 'Result-Start not found-Returns from the start of the @FullString'
        --  SELECT '>' + dbo.GetStringBetweenMarkers('123456789','34','zz',0,0) + '<' AS 'Result-End found-should get "Invalid length parameter passed to the LEFT or SUBSTRING function."'
END
GO

score 0 · Answer 12 · edited Jun 23 '20 at 08:44

0

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_i-Julie@mail.com'

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

edited Jun 23 '20 at 08:44

Vahid Farahmandian

6,081
7
42
62

answered Sep 23 '16 at 23:05

Priyaqua

11
1

Could you add more of an explanation of what your query is doing, that would make your answer more helpful. Welcome to StackOverflow. – dub stylee Sep 23 '16 at 23:12

score 0 · Answer 13 · edited Feb 16 '22 at 05:46

0

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

edited Feb 16 '22 at 05:46

marc_s

732,580
175
1,330
1,459

answered Feb 22 '17 at 11:48

Sunil Kasana

11

2

Whilst this code snippet is welcome, and may provide some help, it would be [greatly improved if it included an explanation](//meta.stackexchange.com/q/114762) of *how* and *why* this solves the problem. Remember that you are answering the question for readers in the future, not just the person asking now! Please [edit] your answer to add explanation, and give an indication of what limitations and assumptions apply. – Toby Speight Feb 22 '17 at 12:38

score 0 · Answer 14 · edited Feb 05 '19 at 09:43

0

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

edited Feb 05 '19 at 09:43

tuomastik

4,559
5
36
48

answered Feb 05 '19 at 09:34

user11016742

1

score 0 · Answer 15 · edited Feb 16 '22 at 05:45

0

I needed to get (099) 0000111-> (099) | 0000111 like two different columns.

SELECT 
    SUBSTRING(Phone, CHARINDEX('(', Phone) + 0, (2 + ((LEN(Phone)) - CHARINDEX(')', REVERSE(Phone))) - CHARINDEX('(', Phone))) AS CodePhone,
    LTRIM(SUBSTRING(Phone, CHARINDEX(')', Phone) + 1, LEN(Phone))) AS NumberPhone
FROM 
    Suppliers
WHERE 
    Phone LIKE '%(%)%'

edited Feb 16 '22 at 05:45

marc_s

732,580
175
1,330
1,459

answered Feb 15 '22 at 20:32

AsamiSatough

1

score 0 · Answer 16 · edited Nov 05 '22 at 23:42

I'm a few years behind, but here's what I did to get a string between characters, that are not the same and also in the even you don't find the ending character, to still give you the substring

BEGIN
    DECLARE @TEXT AS VARCHAR(20)
    SET @TEXT='E101465445454-1'
    SELECT SUBSTRING(@TEXT, CHARINDEX('E', @TEXT)+1, CHARINDEX('-',@TEXT)) as 'STR', 
            CAST(CHARINDEX('E', @TEXT)+1 AS INT) as 'val1', CAST(CHARINDEX('-', @TEXT) AS INT) as 'val2',  
            (CAST(CHARINDEX('-',@TEXT) AS INT) - CAST(CHARINDEX('E',@TEXT)+1 AS INT)) as 'SUBTR', LEN(@TEXT) as 'LEN'

    

SELECT CASE WHEN (CHARINDEX('-', @TEXT) > 0) THEN
                                            SUBSTRING(@TEXT, CHARINDEX('E', @TEXT)+1, (CAST(CHARINDEX('-',@TEXT) AS INT) - CAST(CHARINDEX('E',@TEXT)+1 AS INT)))
                ELSE    
                    SUBSTRING(@TEXT, CHARINDEX('E', @TEXT)+1,LEN(@TEXT)- CHARINDEX('E', @TEXT))
          END
END

Try it and comment for any improvements or if it does the job

score -1 · Answer 17 · edited Oct 05 '20 at 13:23

-1

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

edited Oct 05 '20 at 13:23

Giorgi Tsiklauri

9,715
8
45
66

answered Oct 05 '20 at 13:03

Terence Paidamoyo Tichaona Muc

1

2

Consider adding a few words to explain what was wrong in the question. – pascal Oct 05 '20 at 14:20

score -1 · Answer 18 · answered May 05 '21 at 13:46

Let us consider we have a string DUMMY_DATA_CODE_FILE and we want to find out the substring between 2nd and 3rd underscore(_). Then we use query something like this.

select  SUBSTRING('DUMMY_DATA_CODE_FILE',charindex('_', 'DUMMY_DATA_CODE_FILE', (charindex('_','DUMMY_DATA_CODE_FILE', 1))+1)+1, (charindex('_', 'DUMMY_DATA_CODE_FILE', (charindex('_','DUMMY_DATA_CODE_FILE',  (charindex('_','DUMMY_DATA_CODE_FILE', 1))+1))+1)- charindex('_', 'DUMMY_DATA_CODE_FILE', (charindex('_','DUMMY_DATA_CODE_FILE', 1))+1)-1)) as Code

A SQL Query to select a string between two known strings

18 Answers18

Linked

Related