reformatting a string

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!

LVL 15
liebrandAsked:
Who is Participating?
 
rspahitzCommented:
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
 
TimCotteeHead of Software ServicesCommented:
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
 
liebrandAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
K7Commented:
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
 
sharmonCommented:
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
 
rspahitzCommented:
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
 
liebrandAuthor Commented:
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
 
liebrandAuthor Commented:
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
 
rspahitzCommented:
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
 
jklmnCommented:
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
 
liebrandAuthor Commented:
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
 
rspahitzCommented:
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
 
liebrandAuthor Commented:
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
 
sharmonCommented:
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
 
liebrandAuthor Commented:
schweet! works great... thanks
0
 
rspahitzCommented:
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
 
liebrandAuthor Commented:
Yea... thanks Sharmon for all your time on this ! do appreciate it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.