Windows Vista Forums

Extract items between FROM and WHERE

  1. #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. #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. #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. #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. #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