Extracting sub strings from a string

Anything beyond the basics in using the LiveCode language. Share your handlers, functions and magic here.

Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller

Post Reply
Simon Knight
Posts: 919
Joined: Wed Nov 04, 2009 11:41 am

Extracting sub strings from a string

Post by Simon Knight » Thu Nov 07, 2013 5:29 pm

I have a task to maintain an Access Database and as part of that task I wish to identify and document which data tables are used in each query. The Access documenter is not helpful so I decided to copy and process the SQL of each query extracting each reference to a table made by the query. Fortunately the table names all start with the characters "tbl". I have attached the stack and copied my code below, it all works but I am interested if there is a better way of parsing the data.

Code: Select all

On ExtractTableNames
   // Routine that parses an SQL Query and produces a list of tables used by the query
   // Simon Knight Nov 2013
   put empty into fld "output"
   put empty into tOutput
   put fld"SQL" into tSQL
   //Replace the SQL joining characters with Tab
   replace "(" with tab in tSQL
   replace ")" with tab in tSQL
   replace "." with tab in tSQL
   replace "=" with tab in tSQL
   replace ";" with tab in tSQL
   replace "," with tab in tSQL
   replace " " with tab in tSQL
   
   set itemdelimiter to tab
   --Parse each item checking to see if it starts with "tbl"
      repeat for each item theItem in tSQL
         if theItem begins with "tbl"  AND theItem is not among the words of tOutput then
            put theItem & CR after tOutput
         end if
      end repeat
   
   put tOutput into fld"output"
end ExtractTableNames
Attachments
ExtractTableNamesFromSQLQuery.livecode.zip
Zip of livecode stack
(2.07 KiB) Downloaded 307 times
best wishes
Skids

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 10356
Joined: Wed May 06, 2009 2:28 pm

Re: Extracting sub strings from a string

Post by dunbarx » Thu Nov 07, 2013 6:53 pm

Hi.

This seems pretty clean to me.

The only thing I can think of to make it faster, since as you say it already works fine, is to load each of the items 'theItem" into an array preloaded with the "tOutput" items. This would add only those items that do not already exist in that array, doing what your "...and the item is not among the words of tOutput" does. But then again, you would no longer have a distinct list of those new items. They would be merged with the original contents. Not sure if this is important or not, or if it is worth tagging the new items in some way to be able to extract them later.

If speed is adequate, I would have done it the way you already have.

Craig Newman

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7394
Joined: Sat Apr 08, 2006 8:31 pm
Contact:

Re: Extracting sub strings from a string

Post by jacque » Thu Nov 07, 2013 7:53 pm

You could probably do all the replacements with a single line of code by using replaceText. My regex is weak, but it would be something like this:

Code: Select all

get replaceText(tSQL,"\(|\)|\.|=|;|,",tab)
You might not need the backslashes before the parentheses marks, I forget. Thierry would know, he breathes regex. Then I might do something like this:

Code: Select all

split tSQL by tab as set
put the keys of tSQL into tKeys
filter tKeys with "tbl*"
That should give you a list.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 875
Joined: Wed Nov 22, 2006 3:42 pm

Re: Extracting sub strings from a string

Post by Thierry » Thu Nov 07, 2013 8:04 pm

jacque wrote: You could probably do all the replacements with a single line of code by using replaceText. My regex is weak, but it would be something like this:

Thierry would know, he breathes regex.
.
LOL
I'm sorry , I fall into a regex pot when I was young :)

Ok, here is one quick one not tested:

Code: Select all


put ExtractTableNames( fld "SQL") into fld "output"


function ExtractTableNames tSQL
   local tList
   repeat while matchChunk( tSQL, "(?msi)(\btbl\w+)", pStart, pEnd )
      add 1 to tList[char pStart to pEnd of tSQL]
      delete char 1 to pEnd of tSQL
   end repeat
   return the keys of tList
end ExtractTableNames
Thierry
Last edited by Thierry on Thu Nov 07, 2013 9:25 pm, edited 1 time in total.
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!

jacque
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 7394
Joined: Sat Apr 08, 2006 8:31 pm
Contact:

Re: Extracting sub strings from a string

Post by jacque » Thu Nov 07, 2013 9:07 pm

Wow. I'll have to look up all that, Thierry. That's amazing.

Probably you should remove the "then" at the end of the line though. ;)
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 875
Joined: Wed Nov 22, 2006 3:42 pm

Re: Extracting sub strings from a string

Post by Thierry » Thu Nov 07, 2013 9:27 pm

jacque wrote:Wow. I'll have to look up all that, Thierry. That's amazing.
Probably you should remove the "then" at the end of the line though. ;)
Oups. typo!
I did remove it, thanks.
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 875
Joined: Wed Nov 22, 2006 3:42 pm

Re: Extracting sub strings from a string

Post by Thierry » Fri Nov 08, 2013 7:47 am

jacque wrote:Wow. I'll have to look up all that, Thierry. That's amazing.
Jacque,

One of the best place to learn *peacefully* how regular expression works:

http://www.regular-expressions.info/tutorial.html

Regards,

Thierry
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!

Simon Knight
Posts: 919
Joined: Wed Nov 04, 2009 11:41 am

Re: Extracting sub strings from a string

Post by Simon Knight » Fri Nov 08, 2013 4:20 pm

Thanks for all your comments. I have tried the code written by Thierry and it works a treat so I am off to the tutorial to find out how.
best wishes
Skids

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 875
Joined: Wed Nov 22, 2006 3:42 pm

Re: Extracting sub strings from a string

Post by Thierry » Fri Nov 08, 2013 6:37 pm

Simon Knight wrote:I have tried the code written by Thierry and it works a treat so I am off to the tutorial to find out how.
Hi Simon, Jacque and \w+,

This might be interesting to you:

http://forums.runrev.com/viewtopic.php?f=11&t=7462

somehow a very similar approach for a different goal..

Regards,

Thierry
!
SUNNY-TDZ.COM doesn't belong to me since 2021.
To contact me, use the Private messages. Merci.
!

Post Reply