Parse SQL field for specific number 3

[johnpayback]

I need to write a script that can give me the count of a number in a field on a MSSQL 2000 database. Below is the field. It contains more than just one piece of info.

This below is directly from one field in one table of my database. I need to count the occurrences of 20100 in each field like below in my table in my database. Any ideas would be more than I have to start with.

3||0529|20101|1907|0|13:20:17|0|0||T0.1236|20100|1907|0|13:20:20|0|0||T0.1236|20103|1907|0|13:20:52|0|0||T0.1236|

JP

 

[johnpayback]

dm4ever, that looks like it is working but it is painfully slow. Anyway to increase the speed? I'm only trying to pull one days worth of data counts and it took it 30 minutes to complete. How can I keep track of each count as done in some of the other scripts above so I can add them all up and get a total at the end?

JP

 

[sparkbyte]

This may be a silly question at this point, but how come you are doing this with vbscript instead of using an sql query?

just curious.

Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com

 

[johnpayback]

Because I have to parse the data in one field of a table and a single query cannot parse a string inside one field. Or at least as far as I know? That I gave you is one string inside one field of a table in a database. I have to parse that field for each time 20100 is in that one field and count them up. I have to do this for 10,000's of lines in one table. Hope that makes more sense.

JP

 

[jfdabiri]

spark, you don't have to split
the string. just check for
occurences of "20100

 

[dm4ever]

Your code should look like this if you want to use PHV's recommendation.

Const OpenFileForReading = 1
Const OpenFileForWriting = 2
Const OpenFileForAppending = 8
Const adopenstatic = 3
Const adlockoptimistic = 3
Const aduseclient = 3

Dim objFSO
Dim strTemp

Set objFSO = CreateObject("Scripting.FileSystemObject")

strServer = InputBox("Please enter server name: ")
strbdate = InputBox("Please enter begin date (ex. 2005-02-01): ")
stredate = InputBox("Please enter end date (ex. 2005-03-01): ")

Set objconnection = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")
objconnection.open "driver=SQL Server;server="&strserver&";uid=sa;pwd=password;database=DBName;"
objrecordset.open "select tx_CTIData from master_index where filedatetime between '"&strbdate&" 00:00:00.000' and '"&stredate&" 23:59:59.999'" , objconnection, adopenstatic, adlockoptimistic

objRecordSet.MoveFirst
strCounter = 0
Do While objrecordset.eof <> true                               
	strTemp = objRecordset.Fields.Item("tx_CTIData")
	intCount = intCount + (Len(strTemp)-Len(Replace(strTemp,"|20100|","")))/Len("|20100|")
	objrecordset.movenext   
Loop
objRecordSet.Close

WScript.Echo intCount & " accurances found for 20100"

Another possible option

Const OpenFileForReading = 1
Const OpenFileForWriting = 2
Const OpenFileForAppending = 8
Const adopenstatic = 3
Const adlockoptimistic = 3
Const aduseclient = 3

Dim objFSO
Dim strTemp

Set objFSO = CreateObject("Scripting.FileSystemObject")

strServer = InputBox("Please enter server name: ")
strbdate = InputBox("Please enter begin date (ex. 2005-02-01): ")
stredate = InputBox("Please enter end date (ex. 2005-03-01): ")

Set objconnection = CreateObject("ADODB.Connection")
Set objrecordset = CreateObject("ADODB.Recordset")
objconnection.open "driver=SQL Server;server="&strserver&";uid=sa;pwd=password;database=DBName;"
objrecordset.open "select tx_CTIData from master_index where filedatetime between '"&strbdate&" 00:00:00.000' and '"&stredate&" 23:59:59.999'" , objconnection, adopenstatic, adlockoptimistic
strTemp = objrecordset.GetString
objRecordSet.Close
intCount = GetCount(strTemp)
WScript.Echo intCount & " occurances found for 20100"

Function GetCount(strInput)
	Dim RegEx  :  Set RegEx = New RegExp
	RegEx.Pattern = "\|20100\|"
	RegEx.Global = True
	GetCount = RegEx.Execute(strInput).Count
End Function

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid

 

[PHV]

What happens if you do the math in T-SQL ?

...
objrecordset.Open "SELECT SUM((LEN(tx_CTIData)-LEN(REPLACE(tx_CTIData,'|20100|','')))/7) myCount FROM master_index WHERE filedatetime BETWEEN '" & strbdate & " 00:00:00.000' And '" & stredate & " 23:59:59.999'" , objconnection, adopenstatic, adlockoptimistic
strCounter = objrecordset.Fields.Item("myCount")
WScript.Echo strCounter & " occurrences found for 20100"
objRecordSet.Close

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnpayback]

dm4ever, I get the error message below on your first option. Not sure exactly yet why I got this error but I also noticed that the strCounter=0 is not being used. I believe it should be intCount=0 rather than strCounter=0. I'll see if I can get it working.

(26, 5) Microsoft VBScript runtime error: Invalid use of Null: 'Replace'

The second option works great but took about 25 minutes to run for one day's worth of data. About 2500 rows in the database. Great work though. The speed of it isn't that important but the accuracy is. I am very appreciative to you all.

JP

 

[johnpayback]

PHV, that method is extremely fast. My only concern is the number that is generates is different from the other method. I'm not sure why the discrepancy. I'll do some more testing and see if I can find out why.

JP

 

[taupirho]

Actually I think PHV's recommendation was recommended by me a bit earlier (i.e the first posting) . Probably it got overlooked in the general melee and I guess I didn't provide much supporting code either but still ...