0

Do any of you awesome DBA fellows know how to remove both the Date and Time stamp from an SQL string?

For example, I have a string that has this:

"2014-04-17 15:38:53.2389 Unexpected Failure System.ServiceModel.FaultException: Unexpected Failure"

I want to remove the "2014-04-17 15:38:53.2389" and just be left with this:

"Unexpected Failure System.ServiceModel.FaultException: Unexpected Failure"

I can get it to work using SUBSTRING

SUBSTRING(CAST([stacktrace] AS NVARCHAR(500)),25 ,LEN(CAST([stacktrace] AS NVARCHAR(500)))) as stackTrace

But this isn't very elegant and could cause problems for string that don't have dates at the start.

I can find ways to remove the TimeStamp or to remove the Date, but I can't find a way to remove both.

Any help would be much appreciated.

Thanks

Tony L
  • 43
  • 8
  • Thanks to yuirick I was able to apply a REGEX to PATINDEX('%[^- :.0123456789]%', errorString) – Tony L Jul 17 '14 at 10:18

3 Answers3

0

I don't know the exact code, but: It should be possible to filter the string according to the first alphabetic letter in the string.

yuirick
  • 23
  • 7
  • Doesn't really work when we have symbols in the Date Time Stamp and symbols in the error messages – Tony L Jul 17 '14 at 09:43
  • Symbols, alphabetic and numerical characters are three different types of characters, so that shouldn't be a problem. For more info, I found this: http://stackoverflow.com/questions/2110559/sql-to-find-first-non-numeric-character-in-a-string – yuirick Jul 17 '14 at 10:00
0

at least provide a generic format of your error like you are going to get date and timestamp or just time stamp or some text your error always start with

dev
  • 732
  • 2
  • 8
  • 29
  • Not really sure how much more I need to provide. The error either starts with a Date Time stamp as above or doesn't. There are no other connotations – Tony L Jul 17 '14 at 09:42
0

You can use as following:

check if it is date at start and design the substring accordingly under if-else

select isdate(convert(varchar(10),'2014-04-17 15:38:53.100',111))

possibly you can use as 

if (select isdate(convert(varchar(10),<use substring to get first 25 values>,111)))=1
then <design substring to remove and use data>
else 
<use as-is>
Recursive
  • 954
  • 7
  • 12