Using like with three fields - 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

Transact-SQL (2005)

Using like with three fields

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-03-30 : 06:08:09

I have a table

id int,
forename varchar(15),
surname varchar(15),
notes_line_1 varchar(255),
notes_line_2 varchar(255),
notes_line_3 varchar(255)

If I use a query like:

select *
from table
where forename = 'John'
and
(notes_line_1 like 'invoice' or
notes_line_2 like 'invoice' or
notes_line_3 like 'invoice')

only rows with the string 'invoice' in notes_line_1 are returned, even though fields notes_line_2 and notes_line_3 also contain this string.

Where am I going wrong please?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 06:12:33

You aren't using wildcards.
What you've written is effectively:


(notes_line_1 = 'invoice' or
notes_line_2 = 'invoice' or
notes_line_3 = 'invoice')

I think you probably wanted:


(notes_line_1 like '%invoice%' or
notes_line_2 like '%invoice%' or
notes_line_3 like '%invoice%')

AS you said you wanted fields that contain the string.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2010-03-30 : 06:34:13

Many thanks. The wildcard was in my original script, but I omitted to include it in my example posting. Not sure why it only picks up a match within the first field though ...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 06:50:57

Can you post some sample data?

Does the COLLATION of the columns change?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-30 : 13:59:48

quote:
Originally posted by OldMySQLUser

Many thanks. The wildcard was in my original script, but I omitted to include it in my example posting. Not sure why it only picks up a match within the first field though ...

may be that you've some unprintable characters also present in others along with string 'invoice'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources