CODEWITHSUNDEEP
c#asp.net
Rocky
Rocky

Reputation: 4524

how to remove last comma from query C#

I am doing something as follows..

querybuilder = ("SELECT Type, Text FROM [Element] WHERE Id IN( ");
foreach (var item in CaseIdList)
{
   querybuilder += item + ",";
}
querybuilder += ")";

at the last insdie the closing bracket I am getting a comma. How to remove that.

Upvotes: 11

Views: 37483

Answers (11)

Jaiden
Jaiden

Reputation: 18

Try to use Replace():

static void Main()
{
   string temp = "(a, b, c, d,)";
   temp = temp.Replace(",)", ")");

   Console.WriteLine(temp);
   Console.ReadKey();
}

Upvotes: 0

Umesh
Umesh

Reputation: 2732

First of all, if you are concatnating string too many times, you are losing performace because every time you do any operation on string var, it deallocates/allocates the entire string again in memory. You should use StringBuilder in such situations.

StringBuilder querybuilder = New StringBuilder();
querybuilder.Append("SELECT Type, Text FROM [Element] WHERE Id IN( "));
foreach (var item in CaseIdList) 
{    
    //querybuilder += item + ","; 
    querybuilder.Append(item);
    querybuilder.Append(",");
} 
//querybuilder += ")";
querybuilder.Remove(querybuilder.ToString().Length-1,1);
querybuilder.Append(")");

Upvotes: 0

user854301
user854301

Reputation: 5503

Example using StringBuilder:

            var querybuilder  = new StringBuilder("SELECT Type, Text FROM [Element] WHERE Id IN(");
            foreach (var fragment in fragments)
            {
                querybuilder.Append(fragment);
                querybuilder.Append(',');
            }

            // note: we need to delete last line terminator
            querybuilder.Remove(querybuilder.Length - 1, 1);
            querybuilder.Append(')');
            return querybuilder.ToString();

Upvotes: 0

Uriil
Uriil

Reputation: 12628

You can use LINQ Aggregate and string.Format method:

    var items = CaseIdList.Select(p=>p.ToString(CultureInfo.InvariantCulture)).Aggregate((i,j)=>i+", "+j)
    querybuilder = string.Format("SELECT Type, Text FROM [Element] WHERE Id IN ({0})", items);

Upvotes: 0

Jon Skeet
Jon Skeet

Reputation: 1502016

Firstly, I'd try to avoid including the values themselves directly. Use parameters where possible - it's less simple for IN(x, y, z) style parameters, but you can either use a table-valued parameter or simply dynamically create the parameter list (e.g. to IN(@p0, @p1, @p2))

Secondly, I'd avoid using string concatenation in a loop - use StringBuilder if you really have to loop round.

Thirdly, I'd use string.Join to avoid having to loop at all:

string commaSeparated = string.Join(", ", values);

Upvotes: 6

Pieter Germishuys
Pieter Germishuys

Reputation: 4886

You can use String.Join(",", item); This means there is no ugly trimming or splitting that you have to do.

Upvotes: 2

TheEvilPenguin
TheEvilPenguin

Reputation: 5682

I would use String.Join:

querybuilder = "SELECT Type, Text FROM [Element] WHERE Id IN( " + String.Join(",", CaseIdList.ToArray()) + ")";

I would also look into using Parameters instead of constructing SQL with strong concatenation - string concatenation is vulnerable to SQL injection attacks, and parameters are easy to use.

How you would switch to parameters depends on how you're accessing the database, and which database engine, but a quick google search should help.

Upvotes: 1

Tomtom
Tomtom

Reputation: 9394

Instead of the foreach-loop u can use the string.Join method. Take a look at this

Upvotes: 5

Ria
Ria

Reputation: 10347

Try using String.Trim:

Removes all leading and trailing white-space characters from the current String object.

querybuilder = querybuilder.Trim(',');

Upvotes: 0

james31rock
james31rock

Reputation: 2705

querybuilder = ("SELECT Type, Text FROM [Element] WHERE Id IN( ");
foreach (var item in CaseIdList)
{
   querybuilder += item + ",";
}
querbuilder = querybuilder.TrimEnd(',');
querybuilder += ")";

Upvotes: 0

Habib
Habib

Reputation: 223282

use TrimEnd(','); to remove last comma from the string, string.TrimEnd

After the foreach loop use:

querbuilder = querybuilder.TrimEnd(',');

Instead of concatenating string, you may use StringBuilder class.

Upvotes: 25

Related Questions