Extrapolate out data into separate columns from a description field?

Transact-SQL
1

Hello,
Discovered information in an Activity Log file and need to extrapolate out pertinent data from the LogDesription field.

This is the description

Unable to reset Qty to Ship for Item COT40L, Invoice E084152, Sales Order D393606

Need:

  1. COT40L into a separate column As Item (the length of the Item varies) so after the word Item but before the word Invoice
  2. E084152 into a seperate column As Invoice (length is always 7)
  3. D393606 into a seperate column As SalesOrder (length is always 7)

How can this be done?

2

I found Concatenate, however I know it's butchered not really knowing how this works

SELECT RTRIM(LTRIM(REPLACE(REPLACE(SUBSTRING(T.LogDescription, A.InvoiceNo + 7, A.SONo - A.SONo - 7), CHAR(10), ''), CHAR(13), ''))) AS [Sales Order],RTRIM(LTRIM(REPLACE(REPLACE(SUBSTRING(T.LogDescription, A.Item + 7)) AS Item)) AS Log FROM SY_ActivityLog T CROSS APPLY(VALUES(CHARINDEX('Item', T.LogDescription),CHARINDEX('Invoice', T.LogDescription),CHARINDEX('Sales Order', T.LogDescription),)) A (Item, InvoiceNo, SONo);

3

what is producing this Log file? and do you have control over it? the query you provided got issues

create table #low_tech(LogDescription varchar(max))

insert into #low_tech
select 'Unable to reset Qty to Ship for Item COT40L, 
Invoice E084152, Sales Order D39360'
--TRY your query here. Does not work.
select * From #low_tech
drop table #low_tech
4

This is ugly, but I think it will work:

with sy_activitylog(logdescription)
  as (select *
        from (values('Unable to reset Qty to Ship for Item COT40L, Invoice E084152, Sales Order D393606')
                   ,('Unable to reset Qty to Ship for Item COT40L, Invoice E084152, Sales Order D39360')
                   ,('Unable to reset Qty to Ship for Item COT40L, Sales Order D393606, Invoice E084152')
                   ,('Unable to reset Qty to Ship for Sales Order D393606, Invoice E084152, Item COT40L')
                   ,('Unable to reset Qty to Ship for Item COT40Lextra123, Invoice E08415, Sales Order D39360')
                   ,('Unable to reset Qty to Ship for Item COT40Lextra123, Invoice E08415extra123, Sales Order D39360extra123')
             ) as sy_activitylog(logdescription)
     )
select logdescription
      ,replace(substring(logdescription
                        ,charindex('Item ',logdescription)+5
                        ,charindex(' '
                                  ,logdescription+' '
                                  ,charindex('Item ',logdescription)+5
                                  )
                        -charindex('Item ',logdescription)-5
                        )
              ,','
              ,''
              )
       as item
      ,replace(substring(logdescription
                        ,charindex('Invoice ',logdescription)+8
                        ,charindex(' '
                                  ,logdescription+' '
                                  ,charindex('Invoice ',logdescription)+8
                                  )
                        -charindex('Invoice ',logdescription)-8
                        )
              ,','
              ,''
              )
       as invoice
      ,replace(substring(logdescription
                        ,charindex('Sales Order ',logdescription)+12
                        ,charindex(' '
                                  ,logdescription+' '
                                  ,charindex('Sales Order ',logdescription)+12
                                  )
                        -charindex('Sales Order ',logdescription)-12
                        )
              ,','
              ,''
              )
       as salesorder
  from sy_activitylog
;
1 Like
5

Great! You're amazing!
it's working, except looks like need to add one more parameter

Unable to reset Qty to Ship for Item COT40Lextra123, Invoice E08415extra123, Sales Order D39360extra123 COT40Lextra123 E08415extra123 D39360extra123

to exclude or end on the is a digit. if it's a character (extra123), it's not part of the item, invoice or sales order

6

Well it actually IS ending on a digit (3), but I was "just" extending all "fields" to show, it can handle varying lengths.

The "extra123" I added hopefully is NOT a situation you're faced with?

7

oh, ha, lol

ok so this is modified as I need the other fields to display and not sure how to add a WHERE clause?
I need to limit to be able to sort by the extrapolated SalesOrder to >= A*?

select * from SY_ActivityLog select LogDate, CompanyCode, ModuleCode, logdescription ,replace(substring(logdescription ,charindex('Item ',logdescription)+5 ,charindex(' ' ,logdescription+' ' ,charindex('Item ',logdescription)+5 ) -charindex('Item ',logdescription)-5 ) ,',' ,'' ) as item ,replace(substring(logdescription ,charindex('Invoice ',logdescription)+8 ,charindex(' ' ,logdescription+' ' ,charindex('Invoice ',logdescription)+8 ) -charindex('Invoice ',logdescription)-8 ) ,',' ,'' ) as invoice ,replace(substring(logdescription ,charindex('Sales Order ',logdescription)+12 ,charindex(' ' ,logdescription+' ' ,charindex('Sales Order ',logdescription)+12 ) -charindex('Sales Order ',logdescription)-12 ) ,',' ,'' ) as salesorder from sy_activitylog ;

This was my original SQL to view the Log

SELECT [LogDate] ,[LogTime] ,[CompanyCode] ,[UserLogon] ,[ModuleCode] ,[LogDescription] FROM [MAS_SYSTEM].[dbo].[SY_ActivityLog] where [LogDate] >='5/13/2018' and ModuleCode = 'S/O' order by [LogTime] desc

Also getting this Message:
(517273 rows affected)
Msg 537, Level 16, State 2, Line 3
Invalid length parameter passed to the LEFT or SUBSTRING function.

8

Ok so the Message went away after limiting to this

select LogDate, LogTime, CompanyCode, UserLogon, ModuleCode, logdescription from SY_ActivityLog Where LogDate >='1/1/2017' and ModuleCode = 'S/O' and LogDescription like 'Unable to reset Qty to Ship%' select LogDate, LogTime, CompanyCode, ModuleCode, logdescription ,replace(substring(logdescription ,charindex('Item ',logdescription)+5 ,charindex(' ' ,logdescription+' ' ,charindex('Item ',logdescription)+5 ) -charindex('Item ',logdescription)-5 ) ,',' ,'' ) as item ,replace(substring(logdescription ,charindex('Invoice ',logdescription)+8 ,charindex(' ' ,logdescription+' ' ,charindex('Invoice ',logdescription)+8 ) -charindex('Invoice ',logdescription)-8 ) ,',' ,'' ) as invoice ,replace(substring(logdescription ,charindex('Sales Order ',logdescription)+12 ,charindex(' ' ,logdescription+' ' ,charindex('Sales Order ',logdescription)+12 ) -charindex('Sales Order ',logdescription)-12 ) ,',' ,'' ) as salesorder from sy_activitylog Where LogDate >='1/1/2017' and ModuleCode = 'S/O' and LogDescription like 'Unable to reset Qty to Ship%' Order by LogDate asc ;

Hopefully one last question, I need to use this in either Excel or Access
I tried copying the code to Excel Microsoft Query connection but Excel crashes

How do I use this in SQL View and save it as a SQL View? I can then link it via Excel or Access as we need to link on the translated fields to determine why these failed.

Thanks again!

9

Hi,
is there a way to save that as a View?
CREATE VIEW As ActivityLog
above statement does not work

10

Try to create the view without the order by clause

11

Doesn't like the As
removed that and it still doesn't like the select

12
  1. you have 2 select statements that are not joined.
  2. when you post your code back can you put them in the following tag
 [code]your sql code here [/code]
13

it is in code mode
not sure on the two select statements, it's from bitsmed

14

this is code mode.

select LogDate, 
       LogTime, 
	   CompanyCode, 
	   UserLogon, 
	   ModuleCode, 
	   logdescription 
  from SY_ActivityLog 
  Where LogDate >='1/1/2017' 
  and ModuleCode = 'S/O' 
  and LogDescription like 'Unable to reset Qty to Ship%' 
  
  select LogDate, 
         LogTime, 
		 CompanyCode, 
		 ModuleCode, 
		 logdescription ,
		 replace(substring(logdescription ,charindex('Item ',logdescription)+5 ,
charindex(' ' ,logdescription+' ' ,charindex('Item ',logdescription)+5 ) 
-charindex('Item ',logdescription)-5 ) ,',' ,'' ) as item ,
		 replace(substring(logdescription ,charindex('Invoice ',logdescription)+8 ,
charindex(' ' ,logdescription+' ' ,charindex('Invoice ',logdescription)+8 ) 
-charindex('Invoice ',logdescription)-8 ) ,',' ,'' ) as invoice ,
		 replace(substring(logdescription ,
charindex('Sales Order ',logdescription)+12 ,
charindex(' ' ,logdescription+' ' ,charindex('Sales Order ',logdescription)+12 ) 
-charindex('Sales Order ',logdescription)-12 ) ,',' ,'' ) as salesorder 
      from sy_activitylog 
	  Where LogDate >='1/1/2017' 
	    and ModuleCode = 'S/O' 
		and LogDescription like 'Unable to reset Qty to Ship%' 
		Order by LogDate asc ;
15

ok, however I copied and pasted in between and

16

Problem resolved, found out you can use a Pass-through query in Access applying the 2nd select part (also as well as in SQL, 1st select part is not needed apparently)

select LogDate, 
         LogTime, 
		 CompanyCode, 
		 ModuleCode, 
		 logdescription ,
		 replace(substring(logdescription ,charindex('Item ',logdescription)+5 ,
charindex(' ' ,logdescription+' ' ,charindex('Item ',logdescription)+5 ) 
-charindex('Item ',logdescription)-5 ) ,',' ,'' ) as item ,
		 replace(substring(logdescription ,charindex('Invoice ',logdescription)+8 ,
charindex(' ' ,logdescription+' ' ,charindex('Invoice ',logdescription)+8 ) 
-charindex('Invoice ',logdescription)-8 ) ,',' ,'' ) as invoice ,
		 replace(substring(logdescription ,
charindex('Sales Order ',logdescription)+12 ,
charindex(' ' ,logdescription+' ' ,charindex('Sales Order ',logdescription)+12 ) 
-charindex('Sales Order ',logdescription)-12 ) ,',' ,'' ) as salesorder 
      from sy_activitylog 
	  Where LogDate >='1/1/2017' 
	    and ModuleCode = 'S/O' 
		and LogDescription like 'Unable to reset Qty to Ship%' 
		Order by LogDate asc ;