Solved

reformatting a string

Posted on 2001-06-08
17
161 Views
Last Modified: 2010-05-02
I have an need to take this information

begin-select
name                    &name
address                    &address
phone                    &phone
from userinfo
where userid = userid
end-select

and format it to the following (a standard sql query)

select
name,
address,
phone
from userinfo
where userid = userid

What would be the best way of doing this. Keep in mind that the query could change, so it needs to be a method in which it will go through and remove all the &xxxxxxx tags and replace them with commas, and get rid of the extra spacing.

Your assistance would be appreciated!

0
Comment
Question by:liebrand
  • 7
  • 5
  • 2
  • +3
17 Comments
 
LVL 43

Expert Comment

by:TimCottee
ID: 6168265
strOriginal = "begin-select
name                    &name
address                    &address
phone                    &phone
from userinfo
where userid = userid
end-select"

strNew = Replace(Replace(strOriginal,"begin-select","select","&",",")
strNew = Left(strNew,Instr(strNew,"end-select") - 1)

Should return the correct string.
0
 
LVL 15

Author Comment

by:liebrand
ID: 6168296
Not quite...  it landded up looking like this

begin-select
name                    ,name
address                 ,address
phone                   ,phone
from userinfo
where userid = userid
end-select

I need it to look like

select
name,
address,
phone
from userinfo
where userid = userid

0
 
LVL 1

Expert Comment

by:K7
ID: 6168377
Hi
this code is realy messy but it works, i might fix it up a bit and repost it but it is now 2:25 AM where i am so i think i might get soem sleep first

Private Function Convert(Str As String) As String
Dim Str2 As String
Dim Loc1 As Integer
Dim Loc2 As Integer

Str = Replace(Str, "begin-select", "select")
Str = Replace(Str, "end-select", vbNullString)

'remove spaces
Str2 = Str
Do
  Str = Str2
  Str = Replace(Str, "  ", " ")
  Str2 = Replace(Str, "  ", " ")
Loop Until Str = Str2

'rem &xxx tags
Loc1 = InStr(1, Str, "&")
Loc2 = InStr(Loc1, Str, " ")
Do Until Loc1 = 0
   Str = Left$(Str, Loc1 - 1) & Right$(Str, (Len(Str) - Loc2))
   Loc1 = InStr(1, Str, "&")
   If Loc1 = 0 Then Exit Do
   Loc2 = InStr(Loc1, Str, " ")
Loop

Convert = Str
End Function
0
 
LVL 6

Expert Comment

by:sharmon
ID: 6168490
Here is another way....


Sub Test()
  Dim strTest As String
  Dim strFixed As String
 
  strTest = "begin-select" & vbNewLine & _
            "name                    &name" & vbNewLine & _
            "address                    &address" & vbNewLine & _
            "phone                    &phone" & vbNewLine & _
            "From userinfo" & vbNewLine & _
            "Where userid = userid" & vbNewLine & _
            "end-select"
 
  strFixed = FixSQL(strTest)
 
  Debug.Print strFixed
End Sub

Public Function FixSQL(ByVal strOldSQL As String) As String
  Dim i As Integer
  Dim x As Integer
  Dim strBuild As String
  Dim arrQuery() As String
 
  arrQuery = Split(strOldSQL, vbNewLine)
 
  For i = 0 To UBound(arrQuery)
    If InStr(1, arrQuery(i), "begin-select", vbTextCompare) Then
      strBuild = strBuild & "select" & vbNewLine
   
    ElseIf InStr(1, arrQuery(i), "from ", vbTextCompare) Then
      strBuild = strBuild & Trim(arrQuery(i)) & vbNewLine
   
    ElseIf InStr(1, arrQuery(i), "where", vbTextCompare) Then
      strBuild = strBuild & Trim(arrQuery(i))
   
    ElseIf InStr(1, arrQuery(i), "end-select", vbTextCompare) Then
      'Nothing
   
    Else
      x = InStr(1, Trim(arrQuery(i)), "&", vbTextCompare)
      If x <> 0 Then
        strBuild = strBuild & Trim(Left(Trim(arrQuery(i)), x - 1)) _
        & "," & vbNewLine
      Else
        strBuild = strBuild & Trim(arrQuery(i)) & "," & vbNewLine
      End If
    End If
  Next i

  strBuild = Replace(strBuild, ("," & vbNewLine & "from"), _
  (vbNewLine & "From"), 1, -1, vbTextCompare)
 
  FixSQL = strBuild
End Function
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6168526
I see a few steps that are needed:

* Fix the starting & ending lines
* trim all spaces
* locate all substrings starting with "&" and ending with space or end-of-line

The first was already addressed.
The second is probably not necessary.
The third requires a bit of parsing and assumes that there will never be "&" in the final result set.

Assuming that all lines are in an array, SQL_Array:

SQL_Array(0) = replace(SQL_Array(0), "begin-", vbNullString)
SQL_Array(ubound(SQL_Array)) = replace(SQL_Array(ubound(SQL_Array), "end-select", vbNullString)

' pull array elements into a single string (space-delimited)
SQL_Command = join(SQL_Array)

' remove all spaces (there are faster ways...)
TwoSpaces = "  "
While instr(SQL_Command, TwoSpaces) > 0
  SQL_Command = replace(SQL_Command, TwoSpaces, vbNullString)
Wend

' remove the "&" elements
while instr(SQL_Command, "&") > 0
   StartPos = instr(SQL_Command, "&")
   EndPos = instr(StartPos, SQL_Command, " ")
   if EndPos = 0 then
      EndPos = len(SQL_Command)
   end if
   ' Remove the substring
   SQL_Command = left$(SQL_Command, StartPos-1) & mid$(SQL_Command, EndPos + 1)
wend

msgbox SQL_Command
0
 
LVL 15

Author Comment

by:liebrand
ID: 6168784
sharmon, your way appeared to work... until I tried a different query...  the new query I tried was:

begin-select
name                    &name
address                 &address
phone                   &phone
from userinfo
where userid = userid
and phone like '714%'
end-select

and I got:

select
name,
address,
phone
From userinfo
where userid = useridand phone like '714%',

Also note the extra comma at the end that should be there.

0
 
LVL 15

Author Comment

by:liebrand
ID: 6168800
I added a few more things to the query and i noticed that every line had a comma on it where there was a line break... for example

begin-select
name                    &name
address                    &address
phone                    &phone
from userinfo
where userid = userid
and phone like '714%'
and address like 'ROSE%'
end-select

I landed up with the following:

select
name,
address,
phone
From userinfo
where userid = useridand phone like '714%',
and address like 'ANAHEIM%',

I do not understand why userid and the next line joined together.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6168940
Oh, yeah... mine should have replaced the substring with a comma:

  ' Remove the substring
  SQL_Command = left$(SQL_Command, StartPos-1) & mid$(SQL_Command, EndPos + 1)

should be

  ' Replace the substring with comma
  SQL_Command = left$(SQL_Command, StartPos-1) & "," & mid$(SQL_Command, EndPos + 1)
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 5

Expert Comment

by:jklmn
ID: 6169125
Hi,

How about this:

Private Sub Command1_Click()
Dim strOld As String, strNew As String
strOld = "begin-select" & vbCrLf _
& "name                    &name" & vbCrLf _
& "address                    &address" & vbCrLf _
& "phone                    &phone" & vbCrLf _
& "from userinfo" & vbCrLf _
& "Where userid = userid" & vbCrLf _
& "and phone like '714%'" & vbCrLf _
& "and address like 'ROSE%'" & vbCrLf _
& "end-select"
NewFormat strOld, strNew
Print strNew
End Sub

Private Sub NewFormat(strOld As String, strNew As String)
Dim items As Variant, i As Long
items = Split(strOld, vbCrLf)
strNew = "select" & vbCrLf
For i = 1 To UBound(items) - 1
If InStr(items(i), "&") Then
items(i) = Mid(items(i), 1, InStr(items(i), " ") - 1) & ","
End If
strNew = strNew & items(i) & vbCrLf
Next i
End Sub
0
 
LVL 15

Author Comment

by:liebrand
ID: 6169183
jklmn, yours is almost working... with the exception that the data right before the from should not have a comma at the end.

rspahitz, I cannot seem to get yours to work period, i am not sure what I am doing wrong....


0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6169259
Make a command1 button and copy/paste the below code (slightly modified from my original, including fixing a syntax error AND fixing a problem with a comma before the FROM clause):

Private Sub Command1_Click()
  Dim strSQL As String
  Dim SQL_Array() As String
  Dim SQL_Command As String
  Dim EndPos As Integer
  Dim StartPos As Integer
 
  Const TwoSpaces = "  "
 
  strSQL = "begin-select" & vbCrLf
  strSQL = strSQL & "name                    &name" & vbCrLf
  strSQL = strSQL & "address                    &address" & vbCrLf
  strSQL = strSQL & "phone                    &phone" & vbCrLf
  strSQL = strSQL & "From userinfo" & vbCrLf
  strSQL = strSQL & "Where userid = userid" & vbCrLf
  strSQL = strSQL & "end-select"

  SQL_Array = Split(strSQL, vbCrLf)

  SQL_Array(0) = Replace(SQL_Array(0), "begin-", vbNullString)
  SQL_Array(UBound(SQL_Array)) = Replace(SQL_Array(UBound(SQL_Array)), "end-select", vbNullString)
 
  ' pull array elements into a single string (space-delimited)
  SQL_Command = Join(SQL_Array)
 
  ' remove all spaces (there are faster ways...)
  While InStr(SQL_Command, TwoSpaces) > 0
   SQL_Command = Replace(SQL_Command, TwoSpaces, vbNullString)
  Wend
 
  ' remove the "&" elements
  While InStr(SQL_Command, "&") > 0
    StartPos = InStr(SQL_Command, "&")
    EndPos = InStr(StartPos, SQL_Command, " ")
    If EndPos = 0 Then
       EndPos = Len(SQL_Command)
    End If
    ' Remove the substring
    SQL_Command = Left$(SQL_Command, StartPos - 1) & "," & Mid$(SQL_Command, EndPos + 1)
  Wend
 
  ' remove the comma before the "from" clause
  SQL_Command = Replace(SQL_Command, ",from", " from", , , vbTextCompare)
  MsgBox SQL_Command
End Sub
0
 
LVL 15

Author Comment

by:liebrand
ID: 6169269
rspahitz,

yours works if the string is hardcoded. However, i created a form, and put a text box on it and cut and paste the same query i want converted in there, and passed it through your procedure and it doesnt work, it gave me the following:

select name                    ,address                     from users where uid = uid end-select

Close, but its not there yet. I need to be able to pass different strings through it and they will be different, but the same format.
0
 
LVL 22

Accepted Solution

by:
rspahitz earned 200 total points
ID: 6169282
OK...add a text box with Multiline=true, and change as follows below.  Now you can enter stuff in the textbox and check your results.

Option Explicit

Private Sub Command1_Click()
  MsgBox BuildSQL(Text1.Text)
 
End Sub

Private Function BuildSQL(SQLString As String) As String
  Dim SQL_Array() As String
  Dim SQL_Command As String
  Dim EndPos As Integer
  Dim StartPos As Integer
 
  Const TwoSpaces = "  "
 
  SQL_Array = Split(SQLString, vbCrLf)

  SQL_Array(0) = Replace(SQL_Array(0), "begin-", vbNullString)
  SQL_Array(UBound(SQL_Array)) = Replace(SQL_Array(UBound(SQL_Array)), "end-select", vbNullString)
 
  ' pull array elements into a single string (space-delimited)
  SQL_Command = Join(SQL_Array)
 
  ' remove all spaces (there are faster ways...)
  While InStr(SQL_Command, TwoSpaces) > 0
   SQL_Command = Replace(SQL_Command, TwoSpaces, vbNullString)
  Wend
 
  ' remove the "&" elements
  While InStr(SQL_Command, "&") > 0
    StartPos = InStr(SQL_Command, "&")
    EndPos = InStr(StartPos, SQL_Command, " ")
    If EndPos = 0 Then
       EndPos = Len(SQL_Command)
    End If
    ' Remove the substring
    SQL_Command = Left$(SQL_Command, StartPos - 1) & "," & Mid$(SQL_Command, EndPos + 1)
  Wend
 
  ' remove the comma before the "from" clause
  SQL_Command = Replace(SQL_Command, ",from", " from", , , vbTextCompare)
  'MsgBox SQL_Command
  BuildSQL = SQL_Command
End Function

Private Sub Form_Load()
  Dim strSQL As String
  strSQL = "begin-select" & vbCrLf
  strSQL = strSQL & "name                    &name" & vbCrLf
  strSQL = strSQL & "address                    &address" & vbCrLf
  strSQL = strSQL & "phone                    &phone" & vbCrLf
  strSQL = strSQL & "From userinfo" & vbCrLf
  strSQL = strSQL & "Where userid = userid" & vbCrLf
  strSQL = strSQL & "end-select"
  Text1.Text = strSQL
End Sub

0
 
LVL 6

Expert Comment

by:sharmon
ID: 6169445
As far as the way I gave you not working when you changed the query, all you had to do was make another provision for a key word and you'd be all hooked up, here is is with that "and" in place.....although I am not sure without trying whatelse on here make work for you the same or better cause there is too many to try now:)  Take care....


Option Explicit

Sub Test()
 Dim strTest As String
 Dim strFixed As String
 
 strTest = "begin-select" & vbNewLine & _
           "name                    &name" & vbNewLine & _
           "address                    &address" & vbNewLine & _
           "phone                    &phone" & vbNewLine & _
           "From userinfo" & vbNewLine & _
           "Where userid = userid" & vbNewLine & _
           "and phone like '714%'" & vbNewLine & _
           "end-select"
 
 strFixed = FixSQL(strTest)
 
 Debug.Print strFixed
End Sub

Public Function FixSQL(ByVal strOldSQL As String) As String
 Dim i As Integer
 Dim x As Integer
 Dim strBuild As String
 Dim arrQuery() As String
 
 arrQuery = Split(strOldSQL, vbNewLine)
 
 For i = 0 To UBound(arrQuery)
   If InStr(1, arrQuery(i), "begin-select", vbTextCompare) Then
     strBuild = strBuild & "select" & vbNewLine
   
   ElseIf InStr(1, arrQuery(i), "from ", vbTextCompare) Then
     strBuild = strBuild & Trim(arrQuery(i)) & vbNewLine
   
   ElseIf InStr(1, arrQuery(i), "where ", vbTextCompare) Then
     strBuild = strBuild & Trim(arrQuery(i)) & vbNewLine
   
   ElseIf InStr(1, arrQuery(i), "end-select", vbTextCompare) Then
     'Nothing
   
   ElseIf InStr(1, arrQuery(i), "and ", vbTextCompare) Then
     strBuild = strBuild & Trim(arrQuery(i)) & vbNewLine
   
   Else
     x = InStr(1, Trim(arrQuery(i)), "&", vbTextCompare)
     If x <> 0 Then
       strBuild = strBuild & Trim(Left(Trim(arrQuery(i)), x - 1)) _
       & "," & vbNewLine
     Else
       strBuild = strBuild & Trim(arrQuery(i)) & "," & vbNewLine
     End If
   End If
 Next i

 strBuild = Replace(strBuild, ("," & vbNewLine & "from"), _
 (vbNewLine & "From"), 1, -1, vbTextCompare)
 
 FixSQL = strBuild
End Function

0
 
LVL 15

Author Comment

by:liebrand
ID: 6169452
schweet! works great... thanks
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 6169458
Thanks...and you may want to acknowledge sharmon's work (at least with a thanks), since it looks like we were both converging toward the same answer.
0
 
LVL 15

Author Comment

by:liebrand
ID: 6169527
Yea... thanks Sharmon for all your time on this ! do appreciate it
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now