Search pattern in directory and extract string from files using PowerShell RRS feed

  • Question

  • I have almost 400 .sql files where i need to search for a specific pattern and output the results.



    select * from mydb.ops1_tbl from something1 <other n lines>


    select * from mydb.ops2_tbl from something2 <other n lines>


    select * from mydb.ops3_tbl ,mydb.ops4_tbl where a = b <other n lines>

    Expected result

    file1.sql mydb.ops1_tbl

    file2.sql mydb.ops2_tbl

    file3.sql mydb.ops3_tbl mydb.ops4_tbl

    Below script in powershell - able to fetch the filename

    Get-ChildItem -Recurse -Filter *.sql|Select-String -pattern "mydb."|group path|select name

    Below script in powershell - able to fetch the line

    Get-ChildItem -Recurse -Filter *.sql | Select-String -pattern "mydb." |select line

    I need in the above format, someone has any pointers regarding this?

    Rakesh Jayaram http://blogs.msdn.com/b/rakesh_ramblings/

    Thursday, January 24, 2019 8:58 PM

All replies

  • You'll have better chance to get reply from the PowerShell forum.
    Friday, January 25, 2019 6:09 AM
  • Hi Rakesh M J,

    Thank you for posting here.

    Since your question is more related to PoweShell, I will move it to PowerShell forum for suitable support.


    The Visual C# forum discuss and ask questions about the C# programming language, IDE, libraries, samples, and tools.

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 25, 2019 6:15 AM
  •  Select-String -Path *.sql -pattern 'mydb\.' | select -ExpandProperty Line

    Pattern is regex pattern and "." is a special character and must be escaped.

    Select-String does the same thing as Get-Content.

    help Select-String -online


    Friday, January 25, 2019 6:32 AM
  • get-childitem -recurse -filter *.sql |
    select-string -pattern 'mydb[^ ]*' -allmatches |
    select-object -property path, @{n='Matches.value'; e={$_.matches.value}}

    Path                             Matches.value
    ----                             -------------
    C:\Users\admin\mydb\file1.sql    mydb.ops1_tbl
    C:\Users\admin\mydb\file2.sql    mydb.ops2_tbl
    C:\Users\admin\mydb\file3.sql    {mydb.ops3_tbl, mydb.ops4_tbl}


    • Edited by JS2010 Monday, January 28, 2019 5:40 PM
    Saturday, January 26, 2019 5:58 PM
  • Isn't that what this does?

    Select-String -Path *.sql -pattern 'mydb\.' | select -ExpandProperty Line

    If you don't escape the"." then any character will match which is not what the user wants … but maybe it is.

    Here is the whole command:

    Select-String -Path *.sql -pattern 'mydb\.'| select Filename,@{n='Line';e={$_.Line}}

    Using a loop with a custom object also works but it is clumsy for PowerShell.


    Saturday, January 26, 2019 6:53 PM
  • get-childitem -recurse -filter *.sql |
    select-string -pattern 'mydb[^ ]*' -allmatches |
    select path, @{n='matches'; e={$_.matches.value}}

    Path matches
    ---- -------
    /Users/js/mydb/file1.sql mydb.ops1_tbl
    /Users/js/mydb/file2.sql mydb.ops2_tbl
    /Users/js/mydb/file3.sql {mydb.ops3_tbl, mydb.ops4_tbl}


    Much better.  Excellent.


    Saturday, January 26, 2019 6:54 PM