x

Query to select a string between two known strings

i want to select a string between two known strings but i dont know the length of the string to be selected like
/a/b/c/d-this text has to be selected-/q/w/e/r
i know that /a/b/c/d- and -/q/w/e/r are constants
more ▼

asked Jun 09, 2011 at 07:07 AM in Default

yogi gravatar image

yogi
215 9 10 12

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first
declare @s varchar(100)
set @s='here is some other text/a/b/c/d-this text has to be selected-/q/w/e/rand here is yet some more text'
select substring(@s,charindex('/a/b/c/d',@s)+len('/a/b/c/d'),charindex('/q/w/e/r',@s)-charindex('/a/b/c/d',@s)-len('/a/b/c/d'))
You could of course do it with Regular Expressions as well, but then you'd have to install a CLR to support the Regex functions.
more ▼

answered Jun 09, 2011 at 07:18 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

You can also try to just replace the two known strings with empty string which will let you get the desired string as a leftover.

select 
    replace(replace(@s, '/a/b/c/d', ''), '/q/w/e/r', '')
The downside of this approach is that if the part of desired value itself also contains your known prefix and/or suffix
Jun 09, 2011 at 01:52 PM Oleg
But that won't cover the string in my example, will it? It will select the text before and after the known strings as well, not just the inbetween string.
Jun 09, 2011 at 02:31 PM Magnus Ahlkvist
@Magnus Ahlkvist No it will not. Your snippet is definitely better. I just made a silly assumption that the desired string is always sandwiched between 2 known strings. If this is not the case then using replace is no good.
Jun 10, 2011 at 11:56 AM Oleg

+1 And as @Magnus Ahlkvist mentioned the Regular Expressions, if you can have multiple occurrences of the strings in the source string and want to return all the instance of the text between the Start/End strings, then the RegEx implementation will the quickest and easiest approach. You can take a look on sample implementation in my post [SQL Server 2005 and SQL Server 2008 Regular Expressions and pattern matching][1]

[1]: http://www.pawlowski.cz/2010/09/sql-server-2005-and-sql-server-2008-regular-expressions-and-pattern-matching-2/
Jun 10, 2011 at 12:44 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
    /* lets assume that SOME strings ARE NULL AND SOME strings
       don't even have the delimiters. Some only have the first, 
       some only have the second and some don't have either. 
       Unfair? Nah, just you wait until this gets into production 
       If either are missing we assume the beginning of the string */
    -- CHARINDEX('-/q/w/e/r',MangledString+'-/q/w/e/r')


    SELECT LEFT(topped, CHARINDEX('-/q/w/e/r',topped+'-/q/w/e/r')-1)
    FROM
      (SELECT 
             RIGHT(mangledString,CHARINDEX(REVERSE('/a/b/c/d-')
                        ,REVERSE('/a/b/c/d-'+MangledString))-1) 
      FROM (VALUES
    ('i know that /a/b/c/d- and -/q/w/e/r are constants'),
    ('i know that there might be just one delimiter -/q/w/e/r '),
    ('/a/b/c/d-i know that there might be just the first one '),
    (NULL),
    ('He who is ridden by a conscience /a/b/c/d-Middle-aged life is merry, and I love to lead it,-/q/w/e/r Because everybody would love to have a baby around who was no  responsibility and lots of fun, '), 
    ('Worries about a lot of nonscience;/a/b/c/d-But there comes a day when your eyes are all right but your arm long enough to hold the telephone book where you can read it, -/q/w/e/rBut I can think of no one but a mooncalf or a gaby'),
    ('He without benefit of scruples/a/b/c/d-And your friends get jocular, so you go to the oculist,-/q/w/e/r Who would trust their own child to raise a baby. '), 
    ('His fun and income soon quadruples/a/b/c/d-And of all your friends he is the joculist,-/q/w/e/rSo you have to personally superintend your grandchild from diapers  to pants and from bottle to spoon,'), 
    ('People live forever in Jacksonville and St. Petersburg and Tampa,  /a/b/c/d-So over his facetiousness let us skim,-/q/w/e/r'), 
    (' But you don''t have to live forever to become a grampa. /a/b/c/d-Only noting that he has been waiting for you ever since you said Good evening to his grandfather clock under the impression that it was him,-/q/w/e/rBecause you know that your own child hasn''t sense enough to come  in out of a typhoon.  '), 
    (' The entrance requirements for grampahood are comparatively mild/a/b/c/d-And you look at his chart and it says SHRDLU QWERTYOP, and you say Well, why SHRDNTLU QWERTYOP? and he says one set of glasses won''t do.-/q/w/e/rYou don''t have to live forever to become a grampa, but if you do  want to live forever,  '), 
    ('You only have to live until your child has a child./a/b/c/d-You need two. -/q/w/e/rDon''t try to be clever; '),
    ('From that point on you start looking both ways over your shoulder/a/b/c/d-One for reading Erle Stanley Gardner''s Perry Mason and Keats''s "Endymion" with,-/q/w/e/r If you wish to reach the end of the trail with an uncut throat'), 
    (' Because sometimes you feel thirty years younger and sometimes  thirty years older.  /a/b/c/d-And the other for walking around without saying Hello to strange wymion with.-/q/w/e/r  Don''t go around saying Quote I don''t mind being a grampa but I  hate being married to a gramma Unquote. '),
    ('Now you begin to realize who it was that reached the height of  imbecility,/a/b/c/d-So you spend your time taking off your seeing glasses to put on your reading glasses, and then remembering that your reading glasses are upstairs or in the car, -/q/w/e/r-/q/w/e/r-/q/w/e/r-/q/w/e/r-/q/w/e/r-/q/w/e/r-/q/w/e/r'),
    (' It was whoever said that grandparents have all the fun and none of  the responsibility./a/b/c/d-And then you can''t find your seeing glasses again because without them on you can''t see where they are. -/q/w/e/r-/q/w/e/rcv e  ewf  we w wer wer wer wer wer we wer we '),
    ('This is the most enticing spiderwebs of a tarradiddle ever spun, /a/b/c/d-Enough of such misshaps, they would try the patience of an ox, -/q/w/e/r

    '),
    ('I prefer to forget both pairs of glasses and pass my declining years saluting strange women and grandfather clocks.'))
      F(MangledString))
    g(topped)

    /*
     and 
    i know that there might be just one delimiter 
    i know that there might be just the first one 
    NULL
    Middle-aged life is merry, and I love to lead it,
    But there comes a day when your eyes are all right but your arm long enough to hold the telephone book where you can read it, 
    And your friends get jocular, so you go to the oculist,
    And of all your friends he is the joculist,
    So over his facetiousness let us skim,
    Only noting that he has been waiting for you ever since you said Good evening to his grandfather clock under the impression that it was him,
    And you look at his chart and it says SHRDLU QWERTYOP, and you say Well, why SHRDNTLU QWERTYOP? and he says one set of glasses won't do.
    You need two. 
    One for reading Erle Stanley Gardner's Perry Mason and Keats's "Endymion" with,
    And the other for walking around without saying Hello to strange wymion with.
    So you spend your time taking off your seeing glasses to put on your reading glasses, and then remembering that your reading glasses are upstairs or in the car, 
    And then you can't find your seeing glasses again because without them on you can't see where they are. 
    Enough of such misshaps, they would try the patience of an ox, 
    I prefer to forget both pairs of glasses and pass my declining years saluting strange women and grandfather clocks.
*/
more ▼

answered Jun 11, 2011 at 02:29 PM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

+1 candy is dandy but liquor is quicker
Jun 11, 2011 at 10:58 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left

As a variation to the answer from @magnus ahlkvist ...

DECLARE @s VARCHAR(100)
DECLARE @start INT
DECLARE @end INT
DECLARE @FirstPatn CHAR(8) 
DECLARE @SecondPatn CHAR(8)

SET @s = 'here is some other text/a/b/c/d-this text has to be selected-/q/w/e/rand here is yet some more text'
SET @FirstPatn = '/a/b/c/d'
SET @SecondPatn = '/q/w/e/r'

SELECT  @start = PATINDEX('%' + @FirstPatn + '%', @s) ,
        @end = PATINDEX('%' + @secondPatn + '%', @s)

SELECT  SUBSTRING(@s, @start + LEN(@firstpatn),
( @end - @start ) - LEN(@secondpatn))
more ▼

answered Jun 09, 2011 at 01:53 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

(comments are locked)
10|1200 characters needed characters left

@Jonathan Allen:

Unless I'm missing something, shouldn't you be subtracting LEN(@firstpatn) from the second part of that equation rather than LEN(@secondpatn) when you're selecting the substring?

e.g.

... SELECT SUBSTRING(@s, @start + LEN(@firstpatn), ( @end - @start ) - LEN(@firstpatn))

It appears that it worked in the example you provided because @FirstPatn and @SecondPatn happened to be the same length.

Other than that, your example made my life a lot easier, thanks for the answer!
more ▼

answered Jul 24, 2012 at 05:29 PM

C-Note gravatar image

C-Note
0

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1840
x5

asked: Jun 09, 2011 at 07:07 AM

Seen: 12832 times

Last Updated: Jul 24, 2012 at 05:29 PM