Removing final "OR" from string

I am building up an SQL query in VB.net and have written a routine to do it dynamically.

I want to include each item in an array (of unknown size) in the where clause, as such:

Dim person(10) as String
Dim strSQL, strWhereClause as String
person(0) = "John"
person(1) = "Steve"
'...

For i = 0 To UBound(menuNames)
    strWhereClause &= "[name] = '" & person(i) & "' OR "
Next

strSQL= "SELECT * FROM [customers] WHERE " & strWhereClause 

The problem here is that there is an extra " 开发者_如何转开发OR " at the end. Is there a neat way of removing this? Or perhaps a better way to approach the problem all together. (I think a string.join will not work in this situation, because there is text before and after the array item.)

EDIT: Manually removing the final three characters like so:strWhereClause = Left(strWhereClause, strWhereClause.Length - 3) is fine, except it doesn't handle the empty string. After all, I don't know how many elements will be in my array.

You should not do this in the way you do, even if you are 100% sure what contents are stored in the array it is still a bad habit. Instead you should get yourself familiar using parameterized queries.

Not compile-tested:

var sb = new StringBuilder();
sb.Append("SELECT * FROM [customers] WHERE ");

for (int i = 0; i < person.Length; ++i)
{
    var param = string.Format("NAME{0}", i);
    sb.Append(string.Format("[NAME] = @{0}{1}", param, i < person.Length - 1 ? " OR " : string.Empty));
    command.Parameters.AddWithValue(param, person[i]);  
}

var sql = sb.ToString();

Assuming you SqlCommand is command. Sorry for the C# but my VB is too rusty, i hope you get the point. ;)

In my opinion, it depends on your objective:
1. Least lines of code / easiest to read code
2. Maximum efficiency

In reality, Option2 only matters if the string becomes very long or you are rebuilding the string many, many times.

For short code, I would build the string as you are doing, then remove the bits you don't need...

Dim person(10) as String
Dim strSQL, strWhereClause as String
person(0) = "John"
person(1) = "Steve"

i = -1
For i = 0 To UBound(menuNames)
    strWhereClause &= " OR [name] = '" & person(i) & "'"
Next
IF (i >= 0) THEN
  strWhereClause = RIGHT(strWhereClause, LEN(strWhereClause) - 3)
  strSQL= "SELECT * FROM [customers] WHERE " & strWhereClause 
END IF

There are two points of inefficiency here...
1. Concatenating the strings each time, to make a new string involves a lot of reallocating memory and copying the string.
2. Trimming the last three characters counts the whole length of the string, then copies nearly the whole length of the string, into a newly allocated memory location

If you do want a little more efficiency, inefficiency 2 from above can be avoided...

Dim person(10) as String
Dim strSQL, strWhereClause as String
person(0) = "John"
person(1) = "Steve"

If (UBound(menuItems) >= 0) THEN
  strWhereClause &= " [name] = '" & person(0) & "'"

  If (UBound(menuItems) >= 1) THEN
    For i = 1 To UBound(menuNames)
        strWhereClause &= " OR [name] = '" & person(i) & "'"
    Next
  END IF

  strSQL= "SELECT * FROM [customers] WHERE" & strWhereClause 
END IF

(This puts the check outside of the loop, you really don't want to check this every itteration.)

Using String.Join will work for this, if you are able to use LINQ as well. I believe that the below should work on dotNet 3.5+

Dim person(2) As String
Dim out As String

person(0) = "Test 1"
person(1) = "Test 2"
person(2) = "Test 3"

out = String.Join(" Or ", person.Select(Function(n) String.Format("[name] = '{0}'", n)).ToArray)

This will produce the following:

[name] = 'Test 1' Or [name] = 'Test 2' Or [name] = 'Test 3'

Just append the 'out' to your SQL statement

With a little extra logic you won't need string replacement:

Dim person(10) as String
Dim strSQL, strWhereClause as String
person(0) = "John"
person(1) = "Steve"
'...

For i = 0 To UBound(menuNames)
    If i = 0 Then
        strWhereClause &= "[name] = '" & person(i) & "'"
    Else
        strWhereClause &= " OR [name] = '" & person(i) & "'"
    EndIf
Next

strSQL= "SELECT * FROM [customers] WHERE " & strWhereClause 

Here is the solution using System.Text.StringBuilder:

Dim person(1) As String
Dim strSQL, strWhereClause As String
person(0) = "John"
person(1) = "Steve"
'...

Dim menuNames(person.Length - 1) As String
Dim Sb As New System.Text.StringBuilder
Dim i As Int32

Sb.Append("SELECT * FROM [customers] WHERE ")
For i = 0 To UBound(menuNames) - 1
    'strWhereClause &= "[name] = '" & person(i) & "' OR "
    Sb.Append("[name] = '")
    Sb.Append(person(i))
    Sb.Append("' OR ")
Next

Sb.Append("[name] = '")
Sb.Append(person(menuNames.Length - 1))
Sb.Append("'")

strSQL = Sb.ToString

NOTE: This solution tested OK with Visual Studio 2010.

add this line to the end:

strWhereClause = strWhereClause.replaceAll(" OR $", "");

oops - that's java. hope you can use it :)

Surely you can just remove the final three characters of the strWhereClause before the sqlStr assignment?

Something like:

Next

strWhereClause = Mid(strWhereClause, 0, Len(strWhereClause) - 3)

strSQL= "SELECT * FROM [customers] WHERE " & strWhereClause 

You also need to handle an exception when there are 0 people.

You can do using Substring function.

strWhereClause = strWhereClause.Substring(0, strWhereClause.Length - 3);

Example above shown use C#.

Don't build the SQL string until the end. Put each "OR" condition in a List<String> and then do a String.Join() with " OR ".