search multiple columns - SQL Server Forums

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 2005 Forums

SQL Server Administration (2005)

search multiple columns

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-05-16 : 08:44:08

Hi

I have a table with 9 fields that I need to check. I want to check if "text1" and "text2" is in any of thoose 9 fields. But I dont want to check the combination of "text1" and "text2" in the same column. I want for example be able to check if "text1" is in column 1 to 9 and if "text2" is in column 1 to 9. Can someone give me a suggestion on how to do this?

shakti
Starting Member

1 Post

Posted - 2008-05-16 : 10:01:57

I hope the following query helps you.

select colid,syscolumns.name from syscolumns
inner join sysobjects on sysobjects.id = syscolumns.id
where sysobjects.name = 'Partmast'

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-05-16 : 10:25:02

something like...


select *
from urTable
where 'text1' in (col1,col2,col3,col4,col5)
   or 'text2' in (col1,col2,col3,col4,col5)

the 'OR' could be an 'AND' of course depending on your requirement?

Em

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-05-16 : 12:13:51

Hi elancaster

Can that query be modified to use LIKE '%text1%'

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-05-17 : 03:09:10

elancasters solution works great, but I really need to be able to use the LIKE '%text1%' in that query. But how do I get that to work? anyone...?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-17 : 03:23:24

quote:
Originally posted by magmo

Hi

I have a table with 9 fields that I need to check. I want to check if "text1" and "text2" is in any of thoose 9 fields. But I dont want to check the combination of "text1" and "text2" in the same column. I want for example be able to check if "text1" is in column 1 to 9 and if "text2" is in column 1 to 9. Can someone give me a suggestion on how to do this?

May be this

select * from YourTable
WHERE PATINDEX('%text1%',COALESCE(field1,'') + '|' + COALESCE(field2,'') + '|'+ COALESCE(field3,'')+ '|' + COALESCE(field4,'')+ '|' + COALESCE(field5,'')+ '|' + COALESCE(field6,'')+ '|' + COALESCE(field7,'') + '|'+ COALESCE(field8,'') + '|'+ COALESCE(field9,''))>0
AND
 PATINDEX('%text2%',COALESCE(field1,'') + '|' + COALESCE(field2,'') + '|'+ COALESCE(field3,'')+ '|' + COALESCE(field4,'')+ '|' + COALESCE(field5,'')+ '|' + COALESCE(field6,'')+ '|' + COALESCE(field7,'') + '|'+ COALESCE(field8,'') + '|'+ COALESCE(field9,''))>0

magmo
Aged Yak Warrior

558 Posts

Posted - 2008-05-17 : 03:42:58

Hi visakh16

That did the trick, Thank you very much! But I'm not very familiar with the WHERE PATINDEX and COALESCE, what do they do?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources