Technical Systems Manager

Scott Abraham

<div>
Used your example and plugged it in to my query:<br><br>SELECT DEVICE<br>&nbsp; &nbsp; &nbsp; ,DESCRIPTION<br>&nbsp; &nbsp; &nbsp; ,SUBSTRING(DESCRIPTION<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ,CHARINDEX('Sequence number = ', DESCRIPTION) + LEN('Sequence number = ') + 1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;,CHARINDEX(' ', SUBSTRING(DESCRIPTION, CHARINDEX('Sequence number = ', DESCRIPTION) + LEN('Sequence number = ') + 1, 20) + ' ') - 1<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;) AS 'SEQUENCE'<br><br>FROM LGEVENTS<br><br>Worked great.&nbsp; Thanks!
</div>


<div>
You're welcome.<br><br>That code also, of course, doesn't require that the 'Sequence number' start at a specific byte, it can be anywhere in the string.
</div>


<div>
I used the code right away to pull the date in a more dynamic way than I had been. &nbsp;I was pulling based off position in the string, but if they change the format of the log file and that position changes, then I would have to redo it. &nbsp;With your code, now I find the date based preceding string (which should not change). &nbsp;If they change the field a little, I should still be good. &nbsp;I had been experimenting with CHARINDEX and could use it to start the substring, but could not use it to end it.&nbsp; Your solution was also a good education.
</div>


<div>
<div class="content wysiwyg-content fr-view">
I am on the struggle bus extracting the sequence number from the following data example:<br><br>DEVICE,DESCRIPTION<br>Meter,Meter event Primary Power Down Time event occurred on meter = 11/29/2019 16:30:55 Sequence number = 6201 User id = 0 Event argument = 00-00<br>Meter,Meter event Primary Power Down Time event occurred on meter = 11/29/2019 21:18:37 Sequence number = 10233 User id = 0 Event argument = 00-00<br>Meter,Meter event Primary Power Down Time event occurred on meter = 11/29/2019 15:17:16 Sequence number = 510 User id = 0 Event argument = 00-00<br>MeteR,Meter event Primary Power Down Time event occurred on meter = 11/29/2019 15:17:23 Sequence number = 513 User id = 0 Event argument = 00-00<br><br>I basics of what I have are:<br>SELECT DEVICE<br>&nbsp; &nbsp; &nbsp; ,DESCRIPTION<br>&nbsp; &nbsp; &nbsp; ,SUBSTRING(DESCRIPTION<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;,101<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;, 4<br>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) AS 'SEQUENCE'<br><br>FROM EVENTS<br><br>That of course cuts off values above 9999 for sequence. &nbsp;I cannot figure out how to have the length stop at the space after the sequence number.
</div>
</div>



I am on the struggle bus extracting the sequence number from the following data example:
DEVICE,DESCRIPTION
Meter,Meter event Primary Power Down Time event occurred on meter = 11/29/2019 16:30:55 Sequence number = 6201 User id = 0 Event argument = 00-00
Meter,Meter event Primary Power Down Time event occurred on meter = 11/29/2019 21:18:37 Sequence number = 10233 User id = 0 Event argument = 00-00
Meter,Meter event Primary Power Down Time event occurred on meter = 11/29/2019 15:17:16 Sequence number = 510 User id = 0 Event argument = 00-00
MeteR,Meter event Primary Power Down Time event occurred on meter = 11/29/2019 15:17:23 Sequence number = 513 User id = 0 Event argument = 00-00
I basics of what I have are:
SELECT DEVICE
      ,DESCRIPTION
      ,SUBSTRING(DESCRIPTION
               ,101
               , 4
            ) AS 'SEQUENCE'
FROM EVENTS
That of course cuts off values above 9999 for sequence.  I cannot figure out how to have the length stop at the space after the sequence number.

Microsoft SQL SUBSTRING variable length LEN string extraction

Microsoft is best known for their Microsoft Windows line of operating systems, the Microsoft Office suite, and the Internet Explorer and Edge web browsers. Microsoft's mission is to "enable people and businesses throughout the world to realize their full potential by creating technology that transforms the way people work, play, and communicate.” Currently, the Windows operating system created by Microsoft powers over a billion devices around the world. On top of their share in the software creation market, Microsoft manufactures hardware, including laptops, phones, and attachments.

Microsoft

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.