3 Replies Latest reply on Aug 4, 2007 7:22 AM by Newsgroup_User

    Split the text of an SQL query

    obouillaud Level 1
      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

      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