Extract items between FROM and WHERE


  1.    25 Jan 2007 #1
    ktmd Guest

    Extract items between FROM and WHERE


    Hello,
    I would like to get a list of tables listed in a SQL SELECT statement. I
    have not figured out how to code it nicely.

    Sample statement:
    SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE ...

    I would like to see the result as:
    table1
    table2
    table3

    Thanks for your help,
    ktmd

      My System SpecsSystem Spec

  2.    26 Jan 2007 #2
    RichS Guest

    RE: Extract items between FROM and WHERE


    Try this

    $sqlcmd = 'SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE
    ....'

    ## set to lower case
    $sqlcmd = $sqlcmd.ToLower()

    ## drop the where and later
    $i = $sqlcmd.Indexof("where")
    $temp = $sqlcmd.substring(0,$i)

    $temp = $temp.Replace("select * from ", "")

    $array = $temp.split(",")

    for ($i=0; $i -lt $array.count; $i++){

    $array[$i] = $array[$i].Trim()

    $j = -1
    $j = $array[$i].Indexof(" ")

    if ($j -ge 0){$array[$i] = $array[$i].substring(0,$j)}

    $array[$i]
    }


    --
    Richard Siddaway

    Please note that all scripts are supplied "as is" and with no warranty


    "ktmd" wrote:

    > Hello,
    > I would like to get a list of tables listed in a SQL SELECT statement. I
    > have not figured out how to code it nicely.
    >
    > Sample statement:
    > SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE ...
    >
    > I would like to see the result as:
    > table1
    > table2
    > table3
    >
    > Thanks for your help,
    > ktmd
    >

      My System SpecsSystem Spec

  3.    26 Jan 2007 #3
    Vasily Gusev Guest

    Re: Extract items between FROM and WHERE


    Another variant using Regex:

    $sqlcmd -match "FROM(.+)WHERE" #this will crop all text between FROM and
    WHERE to $matches[1]
    $m = [regex]::Match($matches[1],"[ ,]([\w\d]+?)[, ]") # select all words
    surrounded by spaces or colons (not quotes)
    while ($m.groups[1].value){$m.groups[1].value; $m=$m.nextmatch()} #iterate
    through all matches

    May look more complicated if you not familiar with regular expressions.

    "ktmd" <ktmd@discussions.microsoft.com> wrote in message
    news:81C9286F-C962-45E1-BC17-C17A1BC26637@microsoft.com...
    > Hello,
    > I would like to get a list of tables listed in a SQL SELECT statement. I
    > have not figured out how to code it nicely.
    >
    > Sample statement:
    > SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE ...
    >
    > I would like to see the result as:
    > table1
    > table2
    > table3
    >
    > Thanks for your help,
    > ktmd
    >


      My System SpecsSystem Spec

  4.    26 Jan 2007 #4
    mikes.net Guest

    Re: Extract items between FROM and WHERE


    Careful. It is easy to forget to make sure you can search across
    lines. On top of that, Powershell is very tricky with newlines. And
    on top of that, sometimes `n is treated exactly as `r`n which is
    treated exactly as [Environment]::Newline.

    One way to make sure your searches can "wrap" is to join all the lines
    after Powershell has finished chomping them and stuffing them
    individually in an array, but before performing the regex: % {
    [string]::join(" ",$_) } There are many other ways to do this, though
    I yet to develop a fully satisfying one.

    Mike


    On Jan 26, 6:47 am, "Vasily Gusev" <xae...@gmail.com> wrote:
    > Another variant using Regex:
    >
    > $sqlcmd -match "FROM(.+)WHERE" #this will crop all text between FROM and
    > WHERE to $matches[1]
    > $m = [regex]::Match($matches[1],"[ ,]([\w\d]+?)[, ]") # select all words
    > surrounded by spaces or colons (not quotes)
    > while ($m.groups[1].value){$m.groups[1].value; $m=$m.nextmatch()} #iterate
    > through all matches
    >
    > May look more complicated if you not familiar with regular expressions.
    >
    > "ktmd" <k...@discussions.microsoft.com> wrote in messagenews:81C9286F-C962-45E1-BC17-C17A1BC26637@microsoft.com...> Hello,
    > > I would like to get a list of tables listed in a SQL SELECT statement. I
    > > have not figured out how to code it nicely.

    >
    > > Sample statement:
    > > SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE ...

    >
    > > I would like to see the result as:
    > > table1
    > > table2
    > > table3

    >
    > > Thanks for your help,
    > > ktmd


      My System SpecsSystem Spec

  5.    26 Jan 2007 #5
    ktmd Guest

    Re: Extract items between FROM and WHERE


    Thanks everyone for the responses. My problem was solved.
    It is good to see multiple techniques to solve the problem.

    ktmd

    "mikes.net" wrote:

    > Careful. It is easy to forget to make sure you can search across
    > lines. On top of that, Powershell is very tricky with newlines. And
    > on top of that, sometimes `n is treated exactly as `r`n which is
    > treated exactly as [Environment]::Newline.
    >
    > One way to make sure your searches can "wrap" is to join all the lines
    > after Powershell has finished chomping them and stuffing them
    > individually in an array, but before performing the regex: % {
    > [string]::join(" ",$_) } There are many other ways to do this, though
    > I yet to develop a fully satisfying one.
    >
    > Mike
    >
    >
    > On Jan 26, 6:47 am, "Vasily Gusev" <xae...@gmail.com> wrote:
    > > Another variant using Regex:
    > >
    > > $sqlcmd -match "FROM(.+)WHERE" #this will crop all text between FROM and
    > > WHERE to $matches[1]
    > > $m = [regex]::Match($matches[1],"[ ,]([\w\d]+?)[, ]") # select all words
    > > surrounded by spaces or colons (not quotes)
    > > while ($m.groups[1].value){$m.groups[1].value; $m=$m.nextmatch()} #iterate
    > > through all matches
    > >
    > > May look more complicated if you not familiar with regular expressions.
    > >
    > > "ktmd" <k...@discussions.microsoft.com> wrote in messagenews:81C9286F-C962-45E1-BC17-C17A1BC26637@microsoft.com...> Hello,
    > > > I would like to get a list of tables listed in a SQL SELECT statement. I
    > > > have not figured out how to code it nicely.

    > >
    > > > Sample statement:
    > > > SELECT * FROM table1, table2 "alias2", table3 as "alias3" WHERE ...

    > >
    > > > I would like to see the result as:
    > > > table1
    > > > table2
    > > > table3

    > >
    > > > Thanks for your help,
    > > > ktmd

    >
    >

      My System SpecsSystem Spec

Extract items between FROM and WHERE

Similar Threads
Thread Forum
Duplicate Inboxes, Drafts, Sent items, Junk email, deleted items
I have 4 email accounts that I'm using in Win Live Mail. Each account has an inbox, a drafts folder, a sent items folder, a junk email folder, and...
Live Mail
Items in Update History - How to change status of items
Following a (re)installation of Windows Vista, I had a series of updates, including one session that included Silverlight, Windows Live Essentials...
Vista installation & setup
Email - Cannot delete items from Deleted Items folder
Error Messge appears in the text part of each email: Message could not be displayed Windows Mail encountered an unexpected problem while displaying...
Vista mail
Recent items - mysterious items appearing
I recently uninstalled several versions of norton including norton 360 and installed NOD32 smart security and NOD32 antivirus as well as Your...
General Discussion
sent items in quick views all unread items folder
L.S. Some of my sent items are placed in the "all unread items"folder of quick view. I have checked and double checked the settings and it all...
Live Mail
IMAP folders - sent items, deleted items etc not synchronising
I can't get windows mail to sync with the sent items / drafts / deleted items in my fastmail IMAP account. For example, if I compose a new email and...
Vista mail
extract vista I can extract vista from the iso file but can not ex
I can extract vista from the iso file but can not extract from the tao file to a dvd. It will burn the dvd but only in the tao format. Thaqnks ...
Vista General

Our Sites
  • Ten Forums
  • Eight Forums
  • Seven Forums
  • Help Me Bake
  • Site Links
  • Contact Us
  • Privacy and Cookies
  • About Us
    Windows Vista Forums is an independent web site and has not been authorized, sponsored, or otherwise approved by Microsoft Corporation. "Windows 10" and related materials are trademarks of Microsoft Corp.

    Designer Media Ltd
    All times are GMT -5. The time now is 12:57.
    .