toniupstny (2/8/2008)
Nice script.A couple of questions for you:
- Shouldn't there be a set of [ ] around the variables in case they contain special characters like blanks or SQL keywords?
- How can you handle special characters in the LIKE comparison if a string you would search for has a %, _, [ ], [^ ] or quotes?
Toni
I've now added the []'s around the table_name and column_name. I'm not sure when the update will be posted but its a fairly simple change.
Change this line
' FROM ' + @Table_Name + ' (NOLOCK) WHERE ' + @Column_Name + ' LIKE ''%' + @Search_For + '%'''
To this line
' FROM [' + @Table_Name + '] (NOLOCK) WHERE [' + @Column_Name + '] LIKE ''%' + @Search_For + '%'''
As far as pattern searching goes if you want to search on a pattern matching character put it in []'s. So for example if you want to search for the literal string 'test%' you would put it as 'test[%]'.
I thought about putting it in code but then you couldn't add extra patterns. For example as it stands you can search on the word 'test' with a single digit number after it by making the search string 'test[0-9]'
Kenneth
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]