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 Live Mail
Items in Update History - How to change status of items Vista installation & setup
Email - Cannot delete items from Deleted Items folder Vista mail
IMAP folders - sent items, deleted items etc not synchronising Vista mail
extract vista I can extract vista from the iso file but can not ex Vista General