Append quotes and comma to build in Clause for SQL

Question

For handling support queries, i am trying to create a small interface that picks a newline delimited string of text from an ASP multiline textbox and gives the output with single quotes and comma adjusted to the text For Example

A1234
A1235
A1236

Would become something that can go into a 'SQL In Clause'

'A1234',
'A1235',
'A1236'

How can I improve the below code to achieve the same?

string[] arr = txtBox.Text.Split(Environment.NewLine.ToCharArray(), StringSplitOptions.RemoveEmptyEntries);

I am aware of the excel formula (="'"&A1&"',") for the same but excel is not available on few machines.

Why not when the user enters the input, just force them to use a delimiter (such as ,) for separating the entries. Like how the USPS tracking tool work: https://tools.usps.com/go/TrackConfirmAction!input.action — C.J., Aug 27 '14 at 15:04

score 2 · Accepted Answer · 2014-08-28T08:56:43.217

If it were me I'd have:

var value = "IN ('" + String.Join("','", txtBox.Lines) + "')";

Or for the values only:

var value = "'" + String.Join("','", txtBox.Lines) + "'";

It's basically joining each line of the textbox (i.e. each value on each line) with ',' This is fine, but you then to ensure there is a ' at the start and end.

Further reading on the Lines property:

http://msdn.microsoft.com/en-us/library/system.windows.forms.textboxbase.lines(v=vs.110).aspx

EDIT:

string[] lines = txt.Split(new Char[] { '\n', '\r' },
                            StringSplitOptions.RemoveEmptyEntries);

var value = "'" + String.Join("','", lines) + "'";

I tried the txtBox.Lines property. My interface is a web interface and i am not able to do the same for an asp textbox. Is there an alternative? — mhn, Aug 27 '14 at 15:11

score 1 · Answer 2 · edited May 23 '17 at 11:57

Check out this answer... Concat all strings inside a List<string> using LINQ

...there's an Agregate() function! never knew that... so you can say

        var txtBoxText = @"A1234
A1235

A1236";
        var result = txtBoxText.Split(
            Environment.NewLine.ToCharArray(),
                StringSplitOptions.RemoveEmptyEntries)
            .Select(x=>"'" + x + "'")
            .Aggregate((i,j)=> i + "," + j);

        Assert.AreEqual(result, @"'A1234','A1235','A1236'");

Append quotes and comma to build in Clause for SQL

2 Answers2

EDIT: