How to parse numeric sql query result with commas

[Skee777]

I am trying to create a comma delimited array from an sql query result. I use a count query to create the ubound on my array, but the actual query is a select group by. The results are returned as one long number. If I loop through the record set using Response.write, I can concantinate the comma and have it written to the screen in the form I am looking for, but I need to define the results as an array. I am some what new to this and cannot see how to write a function to loop through the results and concantinate commas as an array.

Any suggestions?

Why isn't phonetic spelled the way it sounds?

 

[Gorkem]

Hi Skee777,

Good question.. and I have the perfect answer.. not many people use the SPLIT function available to you in ASP/VB.. It does come in handy A LOT (I find anyways)..

the function is very simple.. SPLIT takes a string value and asks you what character is the delimiter. Once you provide that information, it takes the string, devides it up and stores it into an array.

Here is an example:

<%
StringTxt = &quot;Gorkem,John,Mira,Susan&quot;
Names = split(StringTxt,&quot;,&quot

for each Name in Names
response.write Name & &quot;<br>&quot;
next
%>


Hope this helps,

Gorkem.

 

[Gorkem]

Oh yeah.. Just so you don't get confused.. you can also address the array as:

Names(index)

Cheers,

Gorkem.

 

[Skee777]

Gorkem,

Thanks for the suggestion. The problem is that my query result is returned like this. (1 12 34 4 17) without the spaces so it really comes out like (11234417).

What I need to do is parse the result with commas like (1,12,34,4,17). I believe that the split function would remove the commas in your suggestion.

Unless I'm missing something, which wouldn't suprise me, I still feel stumped.

Thanks




Why isn't phonetic spelled the way it sounds?

 

[Gorkem]

Im am sort of lost in what you need.. Maybe because its 4:00 on a Friday..

COuld you post this portion of your code so I can better understand what you are trying to do and give you a solution.

Cheers,

Gorkem.

 

[Skee777]

'Create SQL statement that will count distinct offices'
strSQL = &quot;SELECT COUNT(Distinct OfficeNumber) AS ubound FROM DataPorts WHERE OfficeNumber <> ''&quot;

'Create SQL statement and return only unique office numbers using group by'
strSQL2 = &quot;SELECT OfficeNumber FROM DataPorts WHERE OfficeNumber <> '' GROUP BY OfficeNumber&quot;

'Create a recordset object instance,'
'and execute the SQL statement'
Set rs2 = Server.CreateObject(&quot;ADODB.Recordset&quot
rs2.Open strSQL2, objConn,3,3

'Change count to integer'
Dim upbound
upbound = Cint(rs(&quot;ubound&quot)

'Verify that upbound is an integer'
response.write vartype(upbound) & &quot;<br>&quot;

response.write upbound & &quot;<br>&quot;

Dim oNum
oNum = rs2(&quot;OfficeNumber&quot

'Determin what type oNum is result (vbString)'
Response.write vartype (oNum) & &quot;<br>&quot;

'Write oNum result (11112131723345678)
Do While not rs2.EOF
Response.write rs2(&quot;officenumber&quot
rs2.MoveNext
Loop

'Define arrBranchCode with variable Ubound'
ReDim arrBranchCode(upbound)

'Here is where I fall apart'
'I need the oNum to becom (1,11,12,13,17,23,3,4,5,6,7,8)'

' arrBranchCode = array(oNum)'

' Response.write arrBranchCode'
Do While not rs2.EOF
response.write rs2(&quot;OfficeNumber&quot & &quot;,&quot;
rs2.MoveNext
Loop

I hope this makes some sense. I don't totally understand what I know Basically I need to parse the oNum result to become like this last section as the arrBranchCode

I understand that it is Friday afternoon. I totally agree that perhaps this needs to wait till Monday.

Thanks for any input and thoughts you have.






Why isn't phonetic spelled the way it sounds?

 

[Gorkem]

Ok.. Let me see if I understand correctly.. each office number in each record in RS is something like:

11112131723345678

So.. for instance, you have 3 returned record sets they would be as such:

RECORD OFFICENUMBER
1 11112131723345678
2 11112131723345679
3 11112131723987654


If this is the case, there isn't much you can do unless you change the OFFICE NUMBER's in the Database to a standardized 2 digit value.. (ie 1 = 01)

Since you cannot tell if the numbers are a single digit or a double digit.. inserting comma's dynamically wouldn't be accurate.

If lets say you had a standardized 2 digit representation for the numbers.. you could use something like this:

<%
TempONum = &quot;&quot;

'cycle through oNum and insert a comma after every two characters.
for i = 1 to len(oNum) step 2
TempONum = TempONum & mid(oNum,i,2) & &quot;,&quot;
next
oNum = TempONum

arrBranchCode = spilt(oNum,&quot;,&quot

%>

Hope this has helped

Cheers,

G.