concatenation trouble - 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 2000 Forums

Transact-SQL (2000)

concatenation trouble

Author

Topic

anculutza
Starting Member

4 Posts

Posted - 2006-10-04 : 11:18:43

Hi,

If you look at the two where clauses below, they are supposed to do the same thing (at least this is my opinion). But the first return 55 records and the second,45.
Can you help me to find out where the differences are?

Thank you.

WHERE (
[ID]
+ IsNull([Name],' ')
+ IsNull([Mfg Name],' ')
+IsNull([Short Desc],' ')
+IsNull([Mfg Part No],' ')
+IsNull([Category Code],' ')
+IsNull([Category Name],' ')) NOT LIKE '%'+@substr+'%'

WHERE
[ID] NOT LIKE '%'+@substr+'%' AND
ISNULL([Name],'') NOT LIKE '%'+@substr+'%' AND
ISNULL([Mfg Name],'') NOT LIKE '%'+@substr+'%' AND
ISNULL([Short Desc],'') NOT LIKE '%'+@substr+';%' AND
ISNULL([Mfg Part No],'') NOT LIKE '%'+@substr+'%' AND
ISNULL([Category Code],'') NOT LIKE '%'+@substr+'%' AND
ISNULL([Category Name],'') NOT LIKE '%'+@substr+'%'

anculutza

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-04 : 11:24:45

I think AND should be replaced by OR:

WHERE
[ID] NOT LIKE '%'+@substr+'%' OR
ISNULL([Name],'') NOT LIKE '%'+@substr+'%' OR
ISNULL([Mfg Name],'') NOT LIKE '%'+@substr+'%' OR
ISNULL([Short Desc],'') NOT LIKE '%'+@substr+';%' OR
ISNULL([Mfg Part No],'') NOT LIKE '%'+@substr+'%' OR 
ISNULL([Category Code],'') NOT LIKE '%'+@substr+'%' OR
ISNULL([Category Name],'') NOT LIKE '%'+@substr+'%'

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-04 : 12:16:53

or post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources