Parse SQL field for specific number 3

johnpayback · Mar 27, 2007

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.

Code:

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

taupirho · Mar 28, 2007

Use your database to do it. I don't know MSSQL but in Oracle it would be something like:-

SELECT (length(sourcefield) - length(Replace(sourcefield,search_str)))
/length(search_str)
from your_table

jfdabiri · Mar 28, 2007

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
searchstring = "some search string"

commandstring = "select * from mytable where somefield = " & searchstring & ";"
objRecordSet.Open commandstring, objConnection, 3, 3
objRecordSet.MoveFirst

do while objrecordse.eof <> true
str_data = objRecordset.Fields.Item("db_field")
for i = 1 to len(str_data) - 5
if mid(str_data, 1, 5) = "20100" then
j = j + 1
end if
next
msgbox "found " & j & " occurences of 20100"

objrecordset.movenext
loop

objRecordSet.Close

jfdabiri · Mar 28, 2007

if mid(str_data, 1, 5) = "20100" then
sorry, this should be:
mid(str_data, i, 5) = "20100

monksnake · Mar 28, 2007

Pull the data from the field as jfdabiri did above and use the Split function on that field. Then use a for loop and find each occurance of 20100.

[monkey]

<.

sparkbyte · Mar 28, 2007

I modified jfdabiri code a bit to account for varied field lenghts.

This should work But I didn't have a way to test it againt a DB. It did work against your string value example though.

Hope this helps.

Code:

do while objrecordse.eof <> true                               
str_data = objRecordset.Fields.Item("db_field")   
x = 0
arrField = Split(str_data, "|")
strUcount = UBound(arrField)
WScript.Echo strUcount & " total values"
For i = 0 To strUcount Step 1 
    If (arrField(i)) = "20100" Then 
		strCounter = x + 1
    End If 
Next  
WScript.Echo strCounter & " accurances found for 20100"

objrecordset.movenext   
loop
                                
objRecordSet.Close

Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com

dm4ever · Mar 28, 2007

Just another option:

Code:

Option Explicit

Dim strTemp, RegEx, intCount


strTemp = "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|" & _
		  "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|"

Set RegEx = New RegExp
RegEx.Pattern = "\|20100\|"
RegEx.Global = True
intCount = RegEx.Execute(strTemp).Count

WScript.Echo "Found " & intCount & " occurences of 20100!"

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

sparkbyte · Mar 28, 2007

Cool dm4ever!!

Regex!!

Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com

PHV · Mar 28, 2007

A perhaps faster code:
strTemp = objRecordset.Fields.Item("db_field")
intCount = (Len(strTemp)-Len(Replace(strTemp,"|20100|","")))/Len("|20100|")

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

johnpayback · Mar 28, 2007

I'm going to test and I will let you know the final results soon. All of these options look perfect so let me see which one works best with what I'm doing.

JP

sparkbyte · Mar 28, 2007

Sorry,, Messed up the loop counter.

It should be..

Code:

strField = "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|" &_
"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|" &_
"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|" &_
"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|" &_
"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|" &_
"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|" &_
"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|" &_
"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|" &_
"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|" 

strCounter = 0
arrField = Split(strField, "|")
strUcount = UBound(arrField)
WScript.Echo strUcount & " total values"
For i = 0 To strUcount Step 1 
    If (arrField(i)) = "20100" Then 
'		WScript.Echo (arrField(i))
		strCounter = strCounter + 1
    End If 
Next  
WScript.Echo strCounter & " accurance found for 20100"

Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

243 total values
9 accurance found for 20100

***** script completed *****

Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com

johnpayback · Mar 28, 2007

I'm not able to get this one to work. No errors just doesn't pull the data.

Code:

do while objrecordset.eof <> true                               
str_data = objRecordset.Fields.Item("tx_CTIData")   
strCounter = 0
arrField = Split(strField, "|")
strUcount = UBound(arrField)
WScript.Echo strUcount & " total values"
For i = 0 To strUcount Step 1 
    If (arrField(i)) = "20100" Then 
        strCounter = strCounter + 1
    End If 
Next  
WScript.Echo strCounter & " accurance found for 20100"
objrecordset.movenext   
Loop
objRecordSet.Close

JP

sparkbyte · Mar 28, 2007

Did you use the beginning of jfdabiri script? I had said that I had modified his script.

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
searchstring = "some search string"

commandstring = "select * from mytable where somefield = " & searchstring & ";"
objRecordSet.Open commandstring, objConnection, 3, 3
objRecordSet.MoveFirst

do while objrecordset.eof <> true
str_data = objRecordset.Fields.Item("tx_CTIData")
strCounter = 0
arrField = Split(strField, "|")
strUcount = UBound(arrField)
WScript.Echo strUcount & " total values"
For i = 0 To strUcount Step 1
If (arrField(i)) = "20100" Then
strCounter = strCounter + 1
End If
Next
WScript.Echo strCounter & " accurance found for 20100"
objrecordset.movenext
Loop
objRecordSet.Close

Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com

johnpayback · Mar 29, 2007

Yes, sorry. I had that in the script but didn't post it. I think that I forgot to movefirst before the loop. Let me try again. Sorry for not posting my full script.

JP

johnpayback · Mar 29, 2007

I found the problem. In my sql query I had an extra where clause asking for something that made it impossible for the loop to find. That is my fault. I had forgot to take it out. What I see when I run the script now is this below over and over. I just need it to tell me how many it finds total.

-1 total values
0 accurance found for 20100
-1 total values
0 accurance found for 20100
-1 total values
0 accurance found for 20100
-1 total values
0 accurance found for 20100
-1 total values
0 accurance found for 20100

JP

dm4ever · Mar 29, 2007

If you used SparkBytes code then simply move the following line outside the Do...Loop

WScript.Echo strCounter & " accurance found for 20100"

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

johnpayback · Mar 29, 2007

Here is my version with PHV's recommendation in it. I'll post Sparkbytes in just a minute. I found some mistakes I made so I'll correct those and then post my version of Sparkbyte's.

Code:

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 = (Len(strTemp)-Len(Replace(strTemp,"|20100|","")))/Len("|20100|") 
strCounter = strCounter + 1
objrecordset.movenext   
Loop
WScript.Echo strCounter & " accurances found for 20100"
objRecordSet.Close

JP

johnpayback · Mar 29, 2007

By the way, how can I keep the command from timing out? It is having to loop through 10,000's of records and it is timing out.

JP

dm4ever · Mar 29, 2007

What if you tried this:

Code:

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 = (Len(strTemp)-Len(Replace(strTemp,"|20100|","")))/Len("|20100|")
WScript.Echo intCount & " occurances found for 20100"

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

johnpayback · Mar 29, 2007

So is str_data the same thing as strField in Sparkbyte's method? I can get all of them to work I just can't get a total of everything. I don't need a total for each item...well I do but I need those totals added together at the end for a final total. The final total is what I really need.

JP

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Parse SQL field for specific number 3

IS-IT--Management

Programmer

MIS

MIS

Programmer

Technical User

Technical User

Technical User

MIS

IS-IT--Management

Technical User

IS-IT--Management

Technical User

IS-IT--Management

IS-IT--Management

Technical User

IS-IT--Management

IS-IT--Management

Technical User

IS-IT--Management

Similar threads

Log in

Part and Inventory Search

Sponsor