Jeterson Miranda Gomes
Jeterson Miranda Gomes

Reputation: 5513

How to extract parameter names from SQL query

I have a SQL query

select * from table where field1 = :param1 and field2 = :param2

I want to get the parameter names from this string. For example: [param1, param2]

How to do this using c# language?

Upvotes: 0

Views: 1287

Answers (2)

I think that's one of the ways to solve the problem.

Regex Info https://learn.microsoft.com/tr-tr/dotnet/api/system.text.regularexpressions.regex?view=netframework-4.8

Code :

public static void Main()
{
    string rqx = @"([:@])\w+"; // or only one @"([:])\w+"
    string sql = "select * from table where field1 = :param1 
                                        And field2 = :param2 
                                        And field3 = :param3
                                        And field4 = @param4";
    Match[] matches = Regex.Matches(sql, rqx)
                   .Cast<Match>()
                   .ToArray();


    foreach(var p in matches)
    {
        Console.WriteLine(p);
    }
}

Result :

:param1
:param2
:param3
@param4

Upvotes: 1

Anu Viswan
Anu Viswan

Reputation: 18183

You could use Regex for the purpose. For example,

[:?@](?<Parameter>[\S]+)

Above regular expression would help in retrieving the parameter names. The expression translates to a word that starts with either of (:?@) followed by a non-whitespace word (\S).

Complete Code

var regex = new Regex(@"[:?@](?<Parameter>[\S]+)");
var str = "select * from table where field1 = @param1 and field2 = :param2";
var matchCollection = regex.Matches(str);
var result = matchCollection.Cast<Match>().Select(x => x.Groups["Parameter"].Value);

Upvotes: 6

Related Questions