C# Extension method to Convert into the comma separated-CodePudding

I have some data like name,firstname,surname,std,Rollno.

Using C#, I want to convert this into

('name', 'surname', 'std', 'Rollno')

so that I can use this this data to query into the SQL/MySQL DB like -

SELECT *
FROM Table1 
WHERE UserCommunicationId IN ('name', 'surname', 'std', 'Rollno');

Instead of

SELECT *
FROM Table1 
WHERE UserCommunicationId IN ('name,surname,std,Rollno');

CodePudding user response：

You can try below below logic

public static class SQLQueryExtensions
{
    public static string ColumnFormat(this String str)
    {
        return "("                             //Include first parenthesis 
              string.Join(", ", str.Split().Select(x => $"'{x}'")) //Add single quote to each column
                ")";                          //Include last parenthesis
    }
}

You can do it in one line as well,

var inputStr = "name,firstname,surname,std,Rollno";
var result = "("   string.Join(", ", inputStr.Split().Select(x => $"'{x}'"))   ")";

Try Online

CodePudding user response：

One approach I can come up is that:

Set the whole string into query as a parameter.
Split it in a WITH query.
LEFT JOIN it in the main query.
NOT NULL to check if there's any hit.

I've wrote an example below, but I am Oracle user so I am not sure if these syntax are right, not even tested, just googled around. Only take it as an reference to the explanation of the idea.

WITH RECURSIVE targets (stringBuffer, word) AS (
SELECT
  @Parameter
 ,NULL
UNION ALL
SELECT
  SUBSTRING_INDEX(stringBuffer, ',', -1)
 ,SUBSTRING_INDEX(stringBuffer, ',', 1)
WHERE LENGTH(word) > 0
   OR LENGTH(stringBuffer) > 0      -- I am not really sure about these
)

SELECT *
FROM Table1
LEFT JOIN targets ON targets.word = Table1.UserCommunicationId
WHERE targets.word IS NOT NULL;

Then, in C#, set Parameter for your query command in string like this

string s = "name,firstname,surname,std,Rollno";

CodePudding user response：

Use blow logic, will solve your problem.

string inputStr = "name,firstname,surname,std,Rollno";  
string result = string.Join(",", inputStr.Split(',').Select(x => string.Format("'{0}'", x)).ToList());
Output = 'name','firstname','surname','std','Rollno'