Separating sql parts

[John]

Hi

Bit of a peculiar question. How can I separate the SELECT, FROM , WHERE and
ORDER BY parts of a sql strings into separate strings? The reason is I need
to replace WHERE and ORDER BY sections of SQL for different situations and
it is handy to dissect the sql and then reassemble as needed.

Thanks

Regards

 

[Cor Ligthert[MVP]]

dim MySelectString = "String " & "From " & "Where" etc

Cor

 

[Rick]

I have had to do this type of thing before with sql strings and have just
separated the string using string.split(" "). Then you get an array of
strings with each entry as a single word. Then you find the array index of
the beginning of the part you need and the index of the beginning of the
next part or the end o the array, whichever comes first. If your sql string
contains line feed/cr pairs you also need to split these since there may not
be a space between the words -in this case you would do something like (code
not checked):

Dim mySqlStr as string = "Select * from Orders O where O.Ordernum = 1234"
Dim splitChars() as Char = {" ", vbcrlf}
dim sqlArray as Array(of String) = mySqlStr.split(splitChars,
StringSplitOptions.RemoveEmptyEntries)

This would yield:

Select
*
From
Orders O
Where
O.Ordernum = 1234
....

To get the Select part you would concatinate sqlArray[0] (position of
Select) through sqlArray[2 - 1] (position of From), and add a space between
each one. You can use String.Join for this part

You will want to trim the strings and eliminate blank entries in the Array,
but this can be done in the Split method. You will also want to do a case
insensitive search for Select, From etc. or else convert everything to
UpperCase and then search for SELECT.

if you are using LINQ you could get the index values from a LINQ query.

HTH

Rick

 

[Jack Jackson]

That will only work for very simple SQL statements that don't have
subquery clauses.

To do it correctly, you need a SQL parser. I don't know where you can
find one, but I would start with Google.

 

[Armin Zingler]

That will only work for very simple SQL statements that don't have
subquery clauses.

To do it correctly, you need a SQL parser. I don't know where you can
find one, but I would start with Google.

I read the reply as "why parse an sql instead of concatinating each sql
needed?" (which I would a agree)


Armin

 

[Cor Ligthert[MVP]]

That will only work for very simple SQL statements that don't have
subquery clauses

Did you read the question from John, or are you doing here your own show?

Cor

 

[Michel Posseth [MCP]]

Although it is not my coding style , you just want dynamic SQL ? ,,, Well
you could just concatanete your SQL strings or use place holders in your SQL
strings wich you find and replace , however watch out for the common SQL
injection pitfalls ( create variabel parameters to avoid this ) .


regards

Michel Posseth

 

[Jack Jackson]

Did you read the question from John, or are you doing here your own show?

Sorry, neither, I misread your post.

 

[Cor Ligthert[MVP]]

Although it is not my coding style , you just want dynamic SQL ? ,,, Well

you could just concatanete your SQL strings or use place holders in your
SQL strings wich you find and replace

Neither my style, I could not get the simple words at the moment I wrote the
reply.

Cor

 

[David Glienna]

Ripe for SQL Injection attacks as well. Stored Procedures are the safest,
with Parameterized Queries when they nmight change.

- David