Extracting time from string

Question

Post reply

Extracting time from string

chrismat

SSChasing Mays

Points: 606
More actions
November 4, 2010 at 1:26 pm

#143970

I have a varchar field with strings of numbers and letters. Somewhere in the string is a 24 hour time in the format hh:mm:ss (with leading 0's). I need to find a way to extract that string from anywhere in the field. Any suggestions?

Viewing 4 posts - 1 through 3 (of 3 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

Check out the SubString, PatIndex and CharIndex functions.

Others that may prove useful to this are Left, Right, and Reverse.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

GSquared SSC Guru Points: 260824 More actions · Answer 2

Will there be any other colons in the data? If not, you could use some simple string functions to get the characters around the colons.

Something like this, but with the column instead of a variable:

DECLARE @String VARCHAR(100);

SELECT @String = 'AB 12:30:00 123';

SELECT SUBSTRING(@String, CHARINDEX(':', @String)-2, 8);

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 3

Assuming that no other string that looks like a time can appear in your source string, you can find it with PATINDEX.

SELECT SUBSTRING(Source, PATINDEX('%[0-2][0-9]:[0-5][0-9]:[0-5][0-9]%', Source), 8)

FROM (SELECT 'sjdf;kljas;dfkjasf12:29:53jdkjsdf' AS Source) x

Result is "12:29:53".