Split the text of an SQL query

[obouillaud]

I'd like to make the following :
transform the text (SQL code) of an SQL query into its components... here is an example of what I need :

<cfsavecontent variable="my_query">
SELECT one, two, three FROM table1
WHERE one = 'yipee' or two = 'hi'
group by one, twho, three
order by one desc
</cfsavecontent>

I need a function that returns me a struct (for example) with
my_struct.select = "SELECT one, two, three"
my_struct.from = "FROM table1"
...

The hard part is that there is not always every element (for example no GROUP or no ORDER BY)... so I'm beginning to make some bad stuff with imbricated FindNoCase() and it's starting to me a real mess.

I'm sure there is an alternative, something that can easily split a string each time it contains a "reserved" sql word.
I imagine a split(my_query,"select,from,where,group by, order by") for example...

I need help and suggestions and as usual, sorry for my bad english

[Dan_Bracuk]

Why not do things in the opposite order. Build your clauses (select, from, etc) first and then concatonate them for your query? Empty strings for group by, order by, etc simply won't matter.

[obouillaud]

because the query is already written elsewhere (and stored in a database 🙂

[Newsgroup_User]

i think a regular expression is he way to go in your case...

---
Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com