Substring - T-SQL

Question

I have a string that get's inserted into a table. The size and content of the string varies.

I basically want all characters between two known words (_ Part1ID = and _ Part2ID =).

I want to get everything in between these two strings from the whole string.

I have tried this approach but getting everything before _ Part2ID =.

Anyone know how to make this work in given conditions?

Thank you

It is not clear what you are asking for. Can you provide some examples? — RBarryYoung
– RBarryYoung, Commented Feb 14, 2013 at 15:20
Whilst you're adding examples might Part1 and/or Part2 appear multiple times in the input? If so, how should that be handled? — Damien_The_Unbeliever
– Damien_The_Unbeliever, Commented Feb 14, 2013 at 15:27
Thank you guys for many responses. Mark's answer seems to work for my situation. I will post an example soon to see if there is any better technique to do it. Thanks. :) — 007
– 007, Commented Feb 14, 2013 at 15:54

paul · Accepted Answer · 2013-02-14 15:20:31Z

3

declare @someString nvarchar(200) = 'lkasjdlkasjdl_Part1ID=hereIsTheBitYouWant_Part2IDlksajdlaksdj'

select  substring(@someString, patindex('%_Part1ID%', @someString) + 9, patindex('%_Part2ID%', @someString) - (patindex('%_Part1ID%', @someString) + 9))

answered Feb 14, 2013 at 15:20

paul

22.1k1 gold badge56 silver badges54 bronze badges

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

Comments

user359040 · Accepted Answer · 2013-02-14 15:22:57Z

2

Try:

with a as
(select 'abc_ Part1ID =efg_ Part2ID =jkl' a)
select a,
       substring(a,
                 charindex('_ Part1ID =',a)+len('_ Part1ID ='),
                 charindex('_ Part2ID =',a)-charindex('_ Part1ID =',a)-len('_ Part1ID ='))
from a

answered Feb 14, 2013 at 15:22

user359040

Comments

Kenneth Fisher · Accepted Answer · 2013-02-14 15:22:42Z

2

DECLARE @s varchar(1000)
SET @s = 'this is a test _ Part1ID = of the emergency broadcast _ Part2ID = system'
SELECT SUBSTRING(@s, PATINDEX('%_ Part1ID =%',@s)+11,
            PATINDEX('%_ Part2ID =%',@s)-PATINDEX('%_ Part1ID =%',@s)-11)

answered Feb 14, 2013 at 15:22

Kenneth Fisher

3,82221 silver badges23 bronze badges

1 Comment

Jon Egerton Over a year ago

Including some explanation can be useful to help to OP understand the substance of the code you've posted.

JosephStyons · Accepted Answer · 2013-02-14 15:28:14Z

1

Modify this template with your keywords and field names.

declare @mystring nvarchar(max) = 'some irrelevant text STARTPOINTI Should Return this text to youENDPOINT other irrelevant text';
declare @bookend1 nvarchar(max) = 'STARTPOINT';
declare @bookend2 nvarchar(max) = 'ENDPOINT';
select
  substring
   (
    @mystring
   ,CHARINDEX(@bookend1,@mystring) + len(@bookend1)
   ,charindex(@bookend2,@mystring) - charindex(@bookend1,@mystring) - len(@bookend1)
   )

answered Feb 14, 2013 at 15:28

JosephStyons

59.1k64 gold badges169 silver badges240 bronze badges

Comments

bwperrin · Accepted Answer · 2013-02-14 15:52:05Z

say the string (or string column) were named s, try:

substring(s, nullif(charindex('_ Part1ID = ', s),0) + 12   -- skip tag (length = 12)
           , nullif(charindex('_ Part2ID = ', s),0)
             - (nullif(charindex('_ Part1ID = ', s),0) + 12) )

This assumes that the Part2ID tag will never appear before Part1ID. We do nullif(charindex(...),0) so that the result will rightly be null if we don't find the tag.

If you were to create an inline function, you could be a bit more elegant:

CREATE FUNCTION dbo.fnGetPart ( 
    @s varchar(8000), @tag1 varchar(20), @tag2 varchar(20)
) returns varchar(100) AS
BEGIN
    return (    select substring(@s, i1, i2 - i1)
        from (  select i1, i2 = nullif(charindex(@tag2, @s, i1),0)    -- ', i1' ensures we look after @tag1
            from (select i1 = nullif(charindex(@tag1, @s),0) + len(@tag1)) t ) t )
END

Collectives™ on Stack Overflow

Substring - T-SQL

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related