Get date values from a long string

  • Hi

    I wanted to get the date values from a column.

    create

    table tbl(tbl_id int, description varchar(5000))

    Insert

    into tbl values (999, 'No known cause found for failing.|UKM|9-MAY-2007 15:13:34|Other comments by Paul|SC01|21-MAY-2007 14:13:53|')

    The text in the description column is basically separated by |

    I want to get the 2 date values out from the description column

    The final output should be in 2 rows

    tbl_id             datefield

    999               9-MAY-2007 15:13:34

    999               21-MAY-2007 14:13:53

    Any idea? thanks in advance

    --Marc

  • Hi Marc,

     

    Not sure if this is the best method.

    It uses what some people call a magic numbers table.

    My example does this for a single string value so if you put this in the middle of loop statment that dealt with one string at a time you would be able to get your final result.

    DECLARE @VvcAgencyName VARCHAR(max)

    SELECT @VvcAgencyName = 'No known cause found for failing.|UKM|9-MAY-2007 15:13:34|Other comments by Paul|SC01|21-MAY-2007 14:13:53|'

    CREATE TABLE #Numbers

          (

          [number] [int],

                CONSTRAINT [Index_Numbers] PRIMARY KEY CLUSTERED

                ([number] ASC) ON [PRIMARY]

          ) ON [PRIMARY]

    DECLARE @ii INT

    SELECT @ii=1

    WHILE (@ii<=1000)

    BEGIN

          INSERT INTO #Numbers(NUMBER)

          SELECT @II

          SET @II=@II+1

    END

     

    SELECT

                SUBSTRING(@VvcAgencyName+'|', number,

                CHARINDEX('|', @VvcAgencyName+'|', number) - number)

    FROM #Numbers

    WHERE number <= LEN(@VvcAgencyName)

                AND SUBSTRING('|' + @VvcAgencyName,

                number, 1) = '|'

                AND

                SUBSTRING(@VvcAgencyName+'|', number,

                CHARINDEX('|', @VvcAgencyName+'|', number) - number) like '%-%-%:%:%'

    ORDER BY number

     

    --DROP THE NUMBERS TABLE

    DROP TABLE #Numbers

     

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Chris has the right idea with the Numbers table... except he didn't carry it far enough... we can do the whole table without a loop.

    First, as Chris had in his code, you need a Numbers or "Tally" table.  These magic little helper tables are good for a bunch of different things, so, I'd consider making a permanent one as part of my code aresenal.  Here's how to make a permanent "Tally" table (notice, no loop, even here)...

    --===== Create and populate the Tally table on the fly
     SELECT TOP 11000 --equates to more than 30 years of dates
            IDENTITY(INT,1,1) AS N
       INTO dbo.Tally
       FROM Master.dbo.SysColumns sc1,
            Master.dbo.SysColumns sc2
    --===== Add a Primary Key to maximize performance
      ALTER TABLE dbo.Tally
        ADD CONSTRAINT PK_Tally_N 
            PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
    --===== Allow the general public to use it
      GRANT SELECT ON dbo.Tally TO PUBLIC

    Ok... we only needed 1 row of test data for Chris' solution... for my proposed solution, we need a whole table's worth... and, just for kicks, we'll make all of the dates and Initials random with embedded row numbers in case we need to test anything else... and, we'll use the Tally table to make this test data... of course, this is NOT part of the solution code...

     CREATE TABLE Tbl (Tbl_ID INT PRIMARY KEY CLUSTERED, Description VARCHAR(5000))
     INSERT INTO Tbl
            (Tbl_ID,Description)
     SELECT TOP 5000
            N AS Tbl_ID,
            'Some comment '+CAST(N AS VARCHAR(10))
          + '|'
          + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
          + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
          + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
          + '|'
          + REPLACE(CONVERT(VARCHAR(20),CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME),106),' ','-')+' '
          + CONVERT(VARCHAR(20),CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME),108)
          + '|'
          + 'Other comment '+CAST(N AS VARCHAR(10))
          + '|'
          + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
          + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
          + CHAR(STR(RAND(CAST(NEWID() AS VARBINARY))*25+65))
          + '|'
          + REPLACE(CONVERT(VARCHAR(20),CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME),106),' ','-')+' '
          + CONVERT(VARCHAR(20),CAST(RAND(CAST(NEWID() AS VARBINARY))*3653.0+36524.0 AS DATETIME),108)
       FROM dbo.Tally
      ORDER BY NEWID() --Just ensures some gaps in Tbl_ID through random selection

    ... and now, by doing a qualified cross-join with the Tally table and a slight mod on Chris' good code, we get the whole table solution without using a loop or a Cursor...

     SELECT Tbl_ID, 
            SUBSTRING(yt.Description+'|', t.n,CHARINDEX('|', yt.Description+'|', t.n) - t.n)
       FROM Tbl AS yt,
            dbo.Tally t
      WHERE t.n <= LEN(yt.Description)
        AND SUBSTRING('|' + yt.Description,t.n, 1) = '|'
        AND SUBSTRING(yt.Description+'|', t.n,CHARINDEX('|', yt.Description+'|', t.n) - t.n) like '%-%-%:%:%'
    

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Hi Jeff,

     

    Wow that is a really good way of doing it.

    I like you code it very nice and neat and easy to ready 🙂

     

    I'll be using this in the future most definitly

     

    Thanks for taking the time to look into this further

     

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • You can also use the fnParseString function found here

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

    Use the second version.

     


    N 56°04'39.16"
    E 12°55'05.25"

  • Peter,

    Do you have an example of how to use your fine split function to find dates in a table of strings like the original OP posted?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Heh... Thanks for the nice compliment, Chris...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Just a thought, Marcus. Any chance you can get your hands on the '|' delimited file this field came from?

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

  • Hey there, Greg!  You finally made it!  Welcome to the "mosh pit"

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff. I am glad I "know" someone here, it seems it can get brutal at times, but I appreciate candor.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply