Updating dB w/SQL using charindex & substring???

[dlingo]

I am trying to update a dB that has fields looking like this:

161/87 (75%)

I want this field to be seperated into three seperate fields

161.........87............75%

Here is my code.....

SQL_CREATE = "CREATE TABLE TEMP_STAT (Site int "
SQL_SELECT = "SELECT Site "
SQL_INSERT = "INSERT INTO TEMP_STAT (Site "

<%
If outputPacketsStaged = &quot;yes&quot; Then
SQL_SELECT = SQL_SELECT & &quot;, SUBSTRING(PacketsStaged,1,(CHARINDEX('/',PacketsStaged)-1)) AS PacketsStagedAtt&quot;
SQL_SELECT = SQL_SELECT & &quot;, SUBSTRING(PacketsStaged,(CHARINDEX('/',PacketsStaged)+1),(CHARINDEX('(',PacketsStaged)-1)) AS PacketsStagedSucc&quot;
SQL_SELECT = SQL_SELECT & &quot;, SUBSTRING(PacketsStaged,(CHARINDEX('(',PacketsStaged)+1),(CHARINDEX(')',PacketsStaged)-1)) AS PacketsStagedPerc&quot;

SQL_CREATE = SQL_CREATE & &quot;, PacketsStagedAtt int, PacketsStagedSucc int, PacketsStagedPerc VARCHAR(255), PacketsStaged VARCHAR(255) &quot;
SQL_SELECT = SQL_SELECT & &quot;, PacketsStaged &quot;
SQL_INSERT = SQL_INSERT & &quot;, PacketsStagedAtt, PacketsStagedSucc, PacketsStagedPerc, PacketsStaged &quot;
End If

SQL_CREATE = SQL_CREATE & &quot; ) &quot;
set r2 = conn.execute(SQL_CREATE)
SQL_SELECT = SQL_SELECT & &quot;FROM Statistics WHERE Site = &quot;&Site&&quot; AND Statistics.DayAge>=&quot;&frdays&&quot; AND Statistics.DayAge<=&quot;&todays+1&&quot; &quot;

set r3 = Server.CreateObject(&quot;ADODB.Recordset&quot
r3.Open SQL_SELECT, conn, adOpenStatic
numrecs = r3.RecordCount

If numrecs = 0 Then
NoData = &quot;1&quot;

Else
SQL_INSERT = SQL_INSERT & &quot; ) values (&quot;&r3.Fields(&quot;Site&quot&&quot; &quot;
On Error Resume Next
r3.MoveFirst
do while Not r3.eof
r3.MoveNext
loop

I think this code sure work properly, but I keep getting the following error:

Undefined function 'SUBSTRING' in expression.

Can someone please advise. Thanks in advance.

 

[BigBadDave]

Its not 'SUBSTRING' its 'Instr' ok ?? :

<%
sstring = Split(Request(&quot;my_string&quot, &quot; &quot
stringlength = UBound(sstring)
match = &quot;foo&quot;
For x = 0 to stringlength
If Instr(sstring(x),match) Then
Response.Write &quot;Match!!&quot;
End If
Next
%> Regards

Big Dave

davidbyng@hotmail.com


** If I am wrong I am sorry, but i'm only trying to help!! **

 

[dlingo]

Ok, I changed all the 'SUBSTRING' values from the code above to 'INSTR' and I didn't get the error for the 'SUBSTRING' expression, but now I'm getting the error for the 'CHARINDEX' expression.

and two more questions.....
Why is it that you use 'SUBSTRING' for Microsoft SQL and not ASP?
If there's a replacement for CHARINDEX, why is it different than Microsoft SQL?

Thanks for the help!

 

[BigBadDave]

Instr(string,searchstring)
returns a numeric position where search string was found

Mid(string,start,length)
chops string at a start position for a fixed number of characters.

Mid(string,start)
results in a string that has all characters before startpos removed.

Trim(string)
removes all spaces from a string. Regards

Big Dave

davidbyng@hotmail.com


** If I am wrong I am sorry, but i'm only trying to help!! **

 

[BigBadDave]

I don't think you can use charindex Regards

Big Dave

davidbyng@hotmail.com


** If I am wrong I am sorry, but i'm only trying to help!! **