What are the challenges you face when working across database platforms? Take the survey
Options

Help with regex to filter out tables

We're trying to filter out some tables from SQL Data compare using the command line in PowerShell and some regular expressions, and we keep getting syntax errors for them.

We tried the one below, which is supposed to match anything that starts with [dbo].[Broadcast and can have any other characters after that, but it didn't work:

\[dbo\]\.\[Broadcast(?:\]|(?:\\.|[^]\n\r\\])+\])

We modified the one above to the following, and it seems to work but I'd like confirmation that this is indeed the correct way to do this.

\[dbo\]\.\[Broadcast[^]\n\r]+\]


We tried doing this second one to filter out any table that starts with [dbo]., has a string of characters, the string ist, and any characters after that:

\[dbo\]\.\[(?:\\.|[^\n\r\\b])+ist(?:\\.|[^]\n\r\\])+\]

This is the one I need the most help figuring out.

We've tried using the above regular expressions in RegexBuddy, and while they're syntactically correct in that tool, it doesn't work with command line.

To put it simply, we're trying to create a regex that filters out what the following two lines of a stored procedure we use currently does:

NOT LIKE 'Broadcast%'
NOT LIKE '%ist%' 

Best Answer

  • Options
    squigleysquigley Posts: 237 Gold 1
    Good Afternoon!
    Regex tends to be better at matching things versus not matching things. Given that, we believe that a negative lookahead would be the way to go.

    It would look something like which will actually only match the first [​ (which should cause the item to be included as there is a match) but it will only match that when that is NOT followed by dbo.].[Broadcast%​ (using the SQL wild card string to show what I mean rather than it actually being the percent sign)
    ^\[(?!dbo\]\.\[Broadcast.*)


    The second one is similar just having the .* before and after ist:
    ^\[(?!dbo\]\.\[.*ist.*)


    Can you let me know if that helps you out?

    Thanks!

    Sean Quigley | Product Support Engineer | Redgate Software

    Have you visited our Help Center?





Answers

  • Options
    npensanpensa Posts: 5 New member
    squigley said:
    Good Afternoon!
    Regex tends to be better at matching things versus not matching things. Given that, we believe that a negative lookahead would be the way to go.

    It would look something like which will actually only match the first [​ (which should cause the item to be included as there is a match) but it will only match that when that is NOT followed by dbo.].[Broadcast%​ (using the SQL wild card string to show what I mean rather than it actually being the percent sign)
    ^\[(?!dbo\]\.\[Broadcast.*)


    The second one is similar just having the .* before and after ist:
    ^\[(?!dbo\]\.\[.*ist.*)


    Can you let me know if that helps you out?

    Thanks!
    This is exactly what I was looking for!  Thank you!
Sign In or Register to comment.