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
– coopertkm, Commented Aug 21, 2013 at 16:02

orgtigger · Accepted Answer · 2013-08-21 16:39:50Z

72

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, 2013 at 16:39

answered Aug 21, 2013 at 16:33

orgtigger

4,1521 gold badge23 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

samthebrand Over a year ago

This works if the bookends you're looking for only appear once each, but not if they appear more than once.

Data Masseur · Accepted Answer · 2013-08-21 16:36:43Z

29

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, 2013 at 16:36

Data Masseur

1,2139 silver badges15 bronze badges

1 Comment

samthebrand Over a year ago

Not exactly what the OP asked: this answer strips the strings on the edge. But this is a useful snippet.

gofr1 · Accepted Answer · 2016-05-11 11:30:05Z

27

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, 2016 at 11:30

gofr1

16k11 gold badges47 silver badges56 bronze badges

answered May 11, 2016 at 11:12

Mark Roll

5863 gold badges7 silver badges15 bronze badges

3 Comments

John Pietrar Over a year ago

What if u have something like 'aaaaa$bbbbb$cc$ccc$' and you want it to return 2 rows containing bbbbb & ccc?

Mark Roll Over a year ago

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

samthebrand Over a year ago

This doesn't really answer the question, as it assumes the "bookends" of the OPs string are the same character, which isn't true.

BWS · Accepted Answer · 2013-08-21 16:27:50Z

10

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.

edited Aug 21, 2013 at 16:27

answered Aug 21, 2013 at 16:03

BWS

3,85620 silver badges26 bronze badges

Comments

vGHazard · Accepted Answer · 2018-02-06 21:29:28Z

8

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 '

edited Feb 6, 2018 at 21:29

answered Feb 6, 2018 at 21:14

vGHazard

1171 silver badge3 bronze badges

2 Comments

Fandango68 Over a year ago

I like this solution. It even works if you pass CHAR(xx) control characters like NEWLINE and CARRIAGE RETURN

RedBottleSanitizer Over a year ago

Doesn't seem to work for String_ResultString_String test?

heldt · Accepted Answer · 2015-03-27 13:51:30Z

7

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

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

edited Mar 27, 2015 at 13:51

heldt

4,30610 gold badges41 silver badges68 bronze badges

answered Mar 27, 2015 at 13:21

agrawars

791 silver badge1 bronze badge

Comments

Community · Accepted Answer · 2017-05-23 12:09:52Z

6

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, 2017 at 12:09

CommunityBot

11 silver badge

answered Aug 21, 2013 at 16:08

eatonphil

13.9k30 gold badges81 silver badges141 bronze badges

Comments

Rich Benner · Accepted Answer · 2016-05-16 14:24:57Z

5

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

edited May 16, 2016 at 14:24

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

answered May 16, 2016 at 13:49

ganesh nandu raut

511 silver badge1 bronze badge

Comments

Evan M · Accepted Answer · 2013-08-21 16:26:22Z

2

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

answered Aug 21, 2013 at 16:26

Evan M

2,6111 gold badge32 silver badges36 bronze badges

Comments

Mentatmatt · Accepted Answer · 2013-08-21 16:39:00Z

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 · Accepted Answer · 2022-11-06 21:22:32Z

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

tuomastik · Accepted Answer · 2019-02-05 09:43:30Z

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 5, 2019 at 9:43

tuomastik

4,9856 gold badges46 silver badges52 bronze badges

answered Feb 5, 2019 at 9:34

user11016742

1

Comments

Vahid Farahmandian · Accepted Answer · 2020-06-23 08:44:38Z

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 protected]'

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

edited Jun 23, 2020 at 8:44

Vahid Farahmandian

6,6327 gold badges48 silver badges68 bronze badges

answered Sep 23, 2016 at 23:05

Priyaqua

111 bronze badge

1 Comment

dub stylee Over a year ago

Could you add more of an explanation of what your query is doing, that would make your answer more helpful. Welcome to StackOverflow.

marc_s · Accepted Answer · 2022-02-16 05:45:26Z

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, 2022 at 5:45

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Feb 15, 2022 at 20:32

AsamiSatough

1

Comments

marc_s · Accepted Answer · 2022-02-16 05:46:27Z

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, 2022 at 5:46

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Feb 22, 2017 at 11:48

Sunil Kasana

11

1 Comment

Toby Speight Over a year ago

Whilst this code snippet is welcome, and may provide some help, it would be greatly improved if it included an explanation 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.

dodekja · Accepted Answer · 2022-11-05 23:42:52Z

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

Giorgi Tsiklauri · Accepted Answer · 2020-10-05 13:23:59Z

-1

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

edited Oct 5, 2020 at 13:23

Giorgi Tsiklauri

11.5k9 gold badges59 silver badges84 bronze badges

answered Oct 5, 2020 at 13:03

Terence Paidamoyo Tichaona Muc

1

1 Comment

pascal Over a year ago

Consider adding a few words to explain what was wrong in the question.

Aashish Kumar · Accepted Answer · 2021-05-05 13:46:57Z

-1

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

answered May 5, 2021 at 13:46

Aashish Kumar

2,9193 gold badges30 silver badges44 bronze badges

Collectives™ on Stack Overflow

A SQL Query to select a string between two known strings

18 Answers 18

1 Comment

1 Comment

3 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

18 Answers 18

1 Comment

1 Comment

3 Comments

Comments

2 Comments

Comments

Comments

Comments

Comments

Comments

Comments

Comments

1 Comment

Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related