How to search for * ?

Question

Post reply

How to search for * ?

GP Van Eron

SSCarpal Tunnel

Points: 4626
More actions
June 29, 2012 at 3:12 pm

#277139

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

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

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

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

DataAnalyst110 Ten Centuries Points: 1361 More actions · Answer 2

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.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

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 '%*%'

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 4

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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 Ten Centuries Points: 1361 More actions · Answer 8

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!

GP Van Eron SSCarpal Tunnel Points: 4626 More actions · Answer 9

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 10

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

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.

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 12

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

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 13

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)