Replace string in VBScript/ASP Classic

Question

I have the following string in ASP Classic/VBScript:

Y157019=1&Y013759=2&Y032231=5

And I would like to modify the string to this:

Update TABLEX SET Status='1' WHERE Ticket= 'Y157019'; Update TABLEX SET Status='2' WHERE Ticket= 'Y013759'; Update TABLEX SET Status='5' WHERE Ticket= 'Y032231';

I tried using Replace() but could not make it work because the value in the string comes after the name.

What is Y157019=1&Y013759=2&Y032231=5? That's not a script. — rory.ap
– rory.ap, Commented Jul 16, 2015 at 12:10

Bond · Accepted Answer · 2015-07-16 13:44:48Z

4

You can Split() your string into an array of key=value pairs and then further Split() the pairs to extract the key and value individually.

For example:

Const FIELDS = "Y157019=1&Y013759=2&Y032231=5"

a = Split(FIELDS, "&")
s = ""

For Each kv In a
    If InStr(kv, "=") > 0 Then
        k = Split(kv, "=")(0)
        v = Split(kv, "=")(1)
        s = s & "Update TABLEX SET Status='" & v & "' WHERE Ticket= '" & k & "'; "
    End If
Next

WScript.Echo s

edited Jul 16, 2015 at 13:44

answered Jul 16, 2015 at 13:01

Bond

16.3k6 gold badges34 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user692942 · Accepted Answer · 2015-07-17 09:10:48Z

Use a RegExp to split your input (sInp) into key value parts and .Replace the those parts into a template (sTmpl) derived from your desired output (sExp):

Option Explicit

'                        1       2 3       4 5       6
Dim sInp  : sInp      = "Y157019=1&Y013759=2&Y032231=5"
Dim sExp  : sExp      = "Update TABLEX SET Status='1' WHERE Ticket= 'Y157019'; Update TABLEX SET Status='2' WHERE Ticket= 'Y013759'; Update TABLEX SET Status='5' WHERE Ticket= 'Y032231';"
Dim sTmpl : sTmpl     = Join(Array( _
              "Update TABLEX SET Status='$2' WHERE Ticket= '$1'" _
            , "Update TABLEX SET Status='$4' WHERE Ticket= '$3'" _
            , "Update TABLEX SET Status='$6' WHERE Ticket= '$5';" _
), "; ")
Dim reRpl : Set reRpl = New RegExp
reRpl.Pattern = "^([^=]+)=(\d+)&([^=]+)=(\d+)&([^=]+)=(\d+)$"
Dim sAct : sAct = reRpl.Replace(sInp, sTmpl)
WScript.Echo sExp
WScript.Echo sAct
WScript.Echo CStr(sAct = sExp)

output:

cscript 31453580.vbs
Update TABLEX SET Status='1' WHERE Ticket= 'Y157019'; Update TABLEX SET Status='2' WHERE Ticket= 'Y013759'; Update TABLEX SET Status='5' WHERE Ticket= 'Y032231';
Update TABLEX SET Status='1' WHERE Ticket= 'Y157019'; Update TABLEX SET Status='2' WHERE Ticket= 'Y013759'; Update TABLEX SET Status='5' WHERE Ticket= 'Y032231';
True

Adapted to the changed specs:

Option Explicit

Dim aTests : aTests = Array( _
    "Y157019=1&Y013759=2&Y032231=5" _
  , "Y157019=1&Y013759=2" _
  , "Y157019=1" _
  , "" _
)
Dim reRpl : Set reRpl = New RegExp
reRpl.Global  = True
reRpl.Pattern = "([^=]+)=(\d+)"

Dim sInp
For Each sInp In aTests
    WScript.Echo "----", sInp
    Dim oMts : Set oMts = reRpl.Execute(sInp)
    Dim sAct
    If 0 < oMts.Count Then
        ReDim aTmp(oMTS.Count - 1)
        Dim i
        For i = 0 To UBound(aTmp)
            aTmp(i) = reRpl.Replace(oMTS(i).Value, "Update TABLEX SET Status='$2' WHERE Ticket= '$1'")
        Next
        sAct = Join(aTmp, "; ") & ";"
    Else
        sAct = "no match"
    End If
    WScript.Echo sAct
Next

output:

cscript 31453580-2.vbs
---- Y157019=1&Y013759=2&Y032231=5
Update TABLEX SET Status='1' WHERE Ticket= 'Y157019'; Update TABLEX SET Status='2' WHERE Ticket= '&Y013759'; Update TABLEX SET Status='5' WHERE Ticket= '&Y032231';
---- Y157019=1&Y013759=2
Update TABLEX SET Status='1' WHERE Ticket= 'Y157019'; Update TABLEX SET Status='2' WHERE Ticket= '&Y013759';
---- Y157019=1
Update TABLEX SET Status='1' WHERE Ticket= 'Y157019';
----
no match

Thanks for the reply, @Ekkehard, sorry for not saying it at first. But in my example I have 3 fields, but in the real case could be 0 or N.
Nice but what is the point you have to do run it in a For loop anyway so the performance gain is lost surely.

Collectives™ on Stack Overflow

Replace string in VBScript/ASP Classic

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related