Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

'Topping and Tailing' strings in database tables

/*

When you are having to extract just part of a string in TSQL, there are a number of techniques you can use. For a small table, you can encapsulate the process in a scalar function. This is neater, easily tested, and simple to understand. With large tables, you can't use scalar functions and expect good performance, but must use instead in-line expressions involving the built-in string functions such as STUFF, LEFT and SUBSTRING. These can get very tangled, and involve a lot of repetition. To avoid this, it is often better to use an intermediate derived table. You still have the problem of handling all predictable exceptions, such as null strings, missing delimiters or reversed delimiters, and you haven't the comfort of the procedural logic that is available with a scalar function.
Here is a typical problem that seems to crop up fairly often: There is a string column in which someone has embedded the data between delimiters. This tends to happen a lot in logs, or from other monitoring systems. Inevitably, because this is an imperfect world, some strings are bound to be 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. Because it is a log, the table is huge and so you have to do this with in-line expressions.  
The answer I'm offering here is to slice of everything after, and including, the end delimiter; and then slice off everything before and including the begin delimiter. This uses only the LEFT and RIGHT built-in functions for the slicing, with CHARINDEX and REVERSE in support

In our test data, we'll include some Odgen Nash poems, and will see whether we can extract the embedded one that is between delimiters.*/


    
SELECT LEFT(ToppedString, CHARINDEX('<ending>',ToppedString+'<ending>')-1)
                  
--we end up by removing everything after '<ending>'
    
FROM
      
(SELECT --we remove everything before '<beginning>'
            
RIGHT(RawStringWithEmbeddedData,CHARINDEX(REVERSE('<beginning>'),REVERSE('<beginning>'+RawStringWithEmbeddedData))-1)
      
FROM (VALUES
        
('in this data, <beginning> and <ending> are constants and delimit the part you want to extract'),
        (
'There might be just one delimiter <ending> in which case you just want the stuff before ''ending'' '),
        (
'It could be that there is just<beginning> in which case in which you want what''s after it'),
        (
'It could be that <ending>that they are back to front<beginning> in which case you want just what''s AFTER ''beginning'''),
        (NULL),
--should return a NULL
        
(NULL),--Likewise. And now a recitation....
        
('He who is ridden by a conscience <beginning>Middle-aged life is merry, and I love to lead it,<ending> Because everybody would love to have a baby around who was no  responsibility and lots of fun, '),
        (
'Worries about a lot of nonscience;<beginning>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, <ending>But I can think of no one but a mooncalf or a gaby'),
        (
'He without benefit of scruples<beginning>And your friends get jocular, so you go to the oculist,<ending> Who would trust their own child to raise a baby. '),
        (
'His fun and income soon quadruples<beginning>And of all your friends he is the joculist,<ending>So 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,  <beginning>So over his facetiousness let us skim,<ending>'),
        (
' But you don''t have to live forever to become a grampa. <beginning>Only noting that he has been waiting for you ever since you said Good evening to his grandfather clock under the impression that itt was him,<ending>Because 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<beginning>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.<ending>You 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.<beginning>You need two. <ending>Don''t try to be clever; '),
        (
'From that point on you start looking both ways over your shoulder<beginning>One for reading Erle Stanley Gardner''s Perry Mason and Keats''s "Endymion" with,<ending> 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.  <beginning>And the other for walking around without saying Hello to strange wymion with.<ending>  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,<beginning>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, <ending><ending><ending><ending><ending><ending><ending>'),
        (
' It was whoever said that grandparents have all the fun and none of  the responsibility.<beginning>And then you can''t find your seeing glasses again because without them on you can''t see where they are. <ending><ending>cv e  ewf  we w wer wer wer wer wer we wer we '),
        (
'This is the most enticing spiderwebs of a tarradiddle ever spun, <beginning>Enough of such misshaps, they would try the patience of an ox, <ending>

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

        
/*
and
There might be just one delimiter
in which case in which you want what's after it
in which case you want nothing
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.


        */

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.