How to search for * ?

  • Hi there,

    Had interesting task today, to seach sps / views in a db for any containing '*' character.

    So far I have tried the following

    SELECT OBJECT_NAME(id)

    FROM syscomments

    WHERE [text] LIKE '%!*%' ESCAPE '!'

    GROUP BY OBJECT_NAME(id)

    But it still just returns all objects, that definitely do not contain '*' I was hoping the ESCAPE would do the trick, but no luck. Anyone have any suggestions?

    Thank you kindly!

    GP

  • Use CHARINDEX() > 0. Don't use PATINDEX though, same problems as LIKE.

    Basically:

    SELECT * from syscomments where CHARINDEX( [text], '*') > 0.

    Easiest workaround I'm aware of.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Hi Craig - What does the > 0 do in your query? I looked on MSDN to find out what CHARINDEX does. But I haven't seen anything like that in the examples.

  • GP Van Eron (6/29/2012)


    Hi there,

    Had interesting task today, to seach sps / views in a db for any containing '*' character.

    So far I have tried the following

    SELECT OBJECT_NAME(id)

    FROM syscomments

    SELECT OBJECT_NAME(id) , text

    FROM syscomments

    WHERE [text] LIKE '%!*%' ESCAPE '!'

    GROUP BY OBJECT_NAME(id)

    But it still just returns all objects, that definitely do not contain '*' I was hoping the ESCAPE would do the trick, but no luck. Anyone have any suggestions?

    Thank you kindly!

    GP

    Your code seems to work OK in the various AdventureWorks databases.

    You really don't need the ESCAPE, because * is not a wildcard in a like clause, but it should still work OK.

    This should also work OK:

    WHERE [text] LIKE '%*%'

  • DataAnalyst110 (6/29/2012)


    Hi Craig - What does the > 0 do in your query? I looked on MSDN to find out what CHARINDEX does. But I haven't seen anything like that in the examples.

    Checks that it exists. CHARINDEX returns the location in the string that the value was found at. If it's 0, it means the character was unfound. Any value greater than 0 means it found something. Since we don't care WHERE it was, just checking for >0 is enough.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • I may have just realized the problem. Thanks for the reminder Michael, I went off escaping and didn't really think through the allowable characters for the pattern replacements.

    Please remember that comments can also be built as:

    /* */ as well as --.

    What you may want to search for is %SELECT *%... assuming that's what you're trying to find.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Another thin I would change is the where you are doing the search. syscomments if for backward compatiblity for SQL Server 2000. Instead of syscomments you should be using sys.sql_modules.

    http://msdn.microsoft.com/en-us/library/ms175081.aspx

    http://msdn.microsoft.com/en-us/library/ms187997.aspx

  • Evil Kraig F (6/29/2012)


    I may have just realized the problem. Thanks for the reminder Michael, I went off escaping and didn't really think through the allowable characters for the pattern replacements.

    Please remember that comments can also be built as:

    /* */ as well as --.

    What you may want to search for is %SELECT *%... assuming that's what you're trying to find.

    What you may want to search for is %SELECT%*%... assuming that's what you're trying to find. What if the * is on the next line?

  • DataAnalyst110 (6/29/2012)


    Hi Craig - What does the > 0 do in your query? I looked on MSDN to find out what CHARINDEX does. But I haven't seen anything like that in the examples.

    Makes perfect sense, thanks!

  • Thank you everyone for your quick and generous replies! Much appreciated!

  • Lynn Pettis (6/29/2012)


    Evil Kraig F (6/29/2012)


    I may have just realized the problem. Thanks for the reminder Michael, I went off escaping and didn't really think through the allowable characters for the pattern replacements.

    Please remember that comments can also be built as:

    /* */ as well as --.

    What you may want to search for is %SELECT *%... assuming that's what you're trying to find.

    What you may want to search for is %SELECT%*%... assuming that's what you're trying to find. What if the * is on the next line?

    Cause you'll end up finding things like this: SELECT colA, colB, colC /* Hey, Lookit me! */ FROM table where...


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Evil Kraig F (6/29/2012)


    Lynn Pettis (6/29/2012)


    Evil Kraig F (6/29/2012)


    I may have just realized the problem. Thanks for the reminder Michael, I went off escaping and didn't really think through the allowable characters for the pattern replacements.

    Please remember that comments can also be built as:

    /* */ as well as --.

    What you may want to search for is %SELECT *%... assuming that's what you're trying to find.

    What you may want to search for is %SELECT%*%... assuming that's what you're trying to find. What if the * is on the next line?

    Cause you'll end up finding things like this: SELECT colA, colB, colC /* Hey, Lookit me! */ FROM table where...

    Yeppers. And yours will miss this:

    select

    *

    from

    mytable

    where

    ...

    Looks like you miss things you shouldn't or find things you shouldn't. Just means you can't totally automate the changes you are trying to make.

    I should know, I had to do this where I am working now.

  • Lynn Pettis (6/29/2012)


    Looks like you miss things you shouldn't or find things you shouldn't. Just means you can't totally automate the changes you are trying to make.

    I should know, I had to do this where I am working now.

    Agreed, there's no good way out of the issue except for a by-hand review of every proc that has a * in it.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Lynn Pettis (6/29/2012)


    Evil Kraig F (6/29/2012)


    I may have just realized the problem. Thanks for the reminder Michael, I went off escaping and didn't really think through the allowable characters for the pattern replacements.

    Please remember that comments can also be built as:

    /* */ as well as --.

    What you may want to search for is %SELECT *%... assuming that's what you're trying to find.

    What you may want to search for is %SELECT%*%... assuming that's what you're trying to find. What if the * is on the next line?

    Careful, folks... that wil also find SELECT SomeColA * SomeColB and a whole host of other things that you might not expect.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply