Search a SQL string

[TechnoTone]

I need to find the position of the "FROM" keyword in a SQL string but I have to be sure it is the whole word and not part of a word. How can I do this?

 

I'd rather avoid repeated searches taking into account seperating spaces, tabs, CRLF's, etc.

[Robby]

Are you doing this because the Sql string is input by an end-user?

[PlausiblyDamp]

you could always search for " FROM " - note the space either side. Also is there a reason you are trying to parse out an SQL string - there may be an alternative solution

[Robby]

I should've finished my thought;

 

If it is input by end-users then you should limit them to the table name and the conditions. ie.

 

"Select * From " & myTable & " Where " & someCondition

[TechnoTone]

My application has a custom reporting reature whereby the SQL is stored as a string with ReportField tags that I replace when the report is run.

 

This is a quick and dirty solution to allow new reports to be added to the system without having to modify the code. However, the end users will only run the reports - they will not be responsible for creating them.

 

What I want to be able to do from the application is change the SELECT to a SELECT COUNT(*) so that I can first get a count of the number of rows that will be returned. This will let me display a progress bar when the report is being run and saved to a CSV file.

[TechnoTone]

Oh, I could search for " FROM " but what if the from has a TAB or new line before it. This is what I meant in my first post about trying to avoid multiple searches.

 

 

This has given me an idea though. I could first replace all TAB's and CRLF's with a SPACE. That'd work.

[Robby]

If you are the one creating the SQLs then why can't you just add/replace the table names dynamically and leave the rest alone ? (or even have a Case and switch between different variations of a statement)

[danh]

I am sure there are other alternatives for what you are trying to achieve. However the code below uses regular expressions and should help. C# Example of course.

 

string sql = "SELECT tblCustomers.* FROM tblCustomers WHERE CU_ID = @pCU_ID";

 

Regex r = new Regex(@"\s(FROM)\s");

 

Match m = r.Match(sql);

 

Console.WriteLine("{0} : {1}" , m.Index, m.Value);

 

Console.WriteLine(sql.Substring(m.Index + 1));

 

 

m.Index will return you the index of the "white space" char before the "FROM" keyword, therefore (m.Index + 1) will return the 0-based index of the FROM keyword within the string.

[TechnoTone]

Thanks danh - that's exactly what I was looking for. I'll give it a go.