0

I need to parse the audit record of our crm database based on sql server. the audit looks like :

'1~240000.0000~37758.4100~05/18/2016 10:59:00~transactioncurrency,424160c2-b401-e211-a11d-5ef3fcdaf1b7~~~'.

The delimiter is the '~' char. In the example above I want to extract the datetime 05/18/2016 10:59:00, which is the 4th data stored here. The position can vary.

I'll tried to use charindex but it doesn't accept an occurence number of the searched string. I cannot use sql function because I call the sql code from excel, which doesn't let me define function.

Thanks in advance for your help

Ralph
  • 9,284
  • 4
  • 32
  • 42
user2410973
  • 1
  • 1
  • You can, though I hesitate to advise it, use `TRY_CONVERT` on all parts. The part which can convert is the date, all others will return `NULL`. Of course, that would mean first extracting all the parts... – HoneyBadger May 30 '16 at 13:45

1 Answers1

0

This should work just fine if you are taking the Excel / VBA route:

Sub DateTimeFromString()

Dim i As Long
Dim strSQL As String
Dim varArray As Variant

strSQL = "1~240000.0000~37758.4100~05/18/2016 10:59:00~transactioncurrency,424160c2-b401-e211-a11d-5ef3fcdaf1b7~~~"
varArray = Split(strSQL, "~")
For i = LBound(varArray) To UBound(varArray)
    If IsDate(varArray(i)) Then
        MsgBox varArray(i)
    End If
Next i

End Sub

Let me know if you have any questions or problems.

Ralph
  • 9,284
  • 4
  • 32
  • 42
  • doing it in excel would be a good solution ; but there are a lot of other things performed in sql relying on the extraction. I did extract the first parts of the strings 1 by 1 in sql , but there are always cases where my extraction doesn't go far enough. I was hoping that there would be a more powerful string function than the charindex, because I know which item I need to extract, but sometimes it is too far for nested extractions. Thank-you anyway Ralph – user2410973 May 30 '16 at 14:54
  • I guess the addition `(from Excel)` in your title threw me off as well as the `Excel` tag. But now it seems that Excel is actually irrelevant to what you want to achieve and should be removed from the question. In fact, it seems to me that you need a simple UDF to convert a string to a table and then you can use `TRY_CONVERT` as suggested in the comment above. The UDF you are looking for has been asked for many times before and can be found in countless different flavors: http://stackoverflow.com/questions/15585632/how-to-convert-comma-separated-nvarchar-to-table-records-in-sql-server-2005 – Ralph May 30 '16 at 15:05