Dynamic search against all columns of a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic search against all columns of a table

Author

Topic

johnsql
Posting Yak Master

161 Posts

Posted - 2008-01-24 : 11:09:21

Buddies,
Assume I have any table with n columns (n>=1). I'd like to write a stored procedure or function for any table. With a given input substring key, a row of the table is selected if the given input substring key is a substring of at least one column of that row.
What is the best way to do this query?

Thanks,
johnsql

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-24 : 11:18:45

WHERE CHARINDEX(@InpSubStr,Col1+Col2+....+Coln) >0

or
WHERE Col1+Col2+....+Coln LIKE '%' + @InpSubStr +'%'

Also note:-

1.You need to CAST all non varchar fields involved here to varchar before concatination
2.You need to make sure you convert all NULL value columns to blank using ISNULL(Col,'') as concatination with NULL yields NULL

johnsql
Posting Yak Master

161 Posts

Posted - 2008-01-24 : 12:22:08

quote:
Originally posted by johnsql

Buddies,
Assume I have any table with n columns (n>=1). I'd like to write a stored procedure or function for any table. With a given input substring key, a row of the table is selected if the given input substring key is a substring of at least one column of that row.
What is the best way to do this query?

Thanks,
johnsql

Thank you that is great.
I modify my sp so it can work with any table of any column names, any number of columns dynamically.

johnsql
Posting Yak Master

161 Posts

Posted - 2008-01-24 : 16:48:59

quote:
Originally posted by visakh16

WHERE CHARINDEX(@InpSubStr,Col1+Col2+....+Coln) >0

or
WHERE Col1+Col2+....+Coln LIKE '%' + @InpSubStr +'%'

Also note:-

1.You need to CAST all non varchar fields involved here to varchar before concatination
2.You need to make sure you convert all NULL value columns to blank using ISNULL(Col,'') as concatination with NULL yields NULL


Well, I think between +'s, must have ' '. Otherwise, for example of a certain row, col1='GAUB' ,col2='La Roue'

If I input a key such as 'BLA', that row is met. What is not what I expect. So, I have to add ' ' between +'s

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-27 : 11:37:18

yeah...thats true. nice spot. cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-28 : 00:47:46

http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources