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:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
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:
SQL-4-Life
Posting Best Practices[/url]
Numbers / Tally Tables[/url] -
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