LIKE - 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)

Author

Topic

shapper
Constraint Violating Yak Guru

450 Posts

Posted - 2007-02-09 : 07:44:27

Hello,

What is the difference between using:

SELECT [CompanyName]
FROM [Customers]
WHERE [CustomerID] LIKE @CustomerID

And

SELECT CompanyName
FROM Customers
WHERE CustomerID = @CustomerID

I use this last form without LIKE.

Thanks,
Miguel

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-09 : 07:47:41

equal = will do a 'exact' match
like will do a 'starting with' match

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-02-09 : 07:53:39

When LIKE is used without wildcard characters (as in your example), LIKE and = operation gives same results, except that = operator is slightly faster.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

ameet.ayare
Starting Member

6 Posts

Posted - 2007-07-04 : 10:48:59

Hi guys,

I'm trying to use LIKE keyword but this has to be parameterised. For Example:

SELECT *
FROM author
WHERE author_fname LIKE '%@author_fname%' OR author_lname LIKE '%@author_lname%'

But SQL server doesn't seem to recognise that I want to accept fname or lname (which ever is given) as a parameter.

The idea is to find a record that matches either the part or full fname or lname as a parameter.

Please help!

Ameet

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-04 : 10:59:06

1 Always start with a new topic for your questions
2 Try

SELECT *
FROM author
WHERE author_fname LIKE '%'+@author_fname+'%' OR author_lname LIKE '%'+@author_lname+'%'

Madhivanan

Failing to plan is Planning to fail

ameet.ayare
Starting Member

6 Posts

Posted - 2007-07-05 : 10:22:00

Thanks for your reply.

I'll bear that in mind (about starting questions as new topic). Actually I did not start a new topic because my question was in connection to the LIKE comparison operator.

However, the code that you have suggested does not work. I tried it in the query designer and also a stored procedure. The stored procedure doesn't give any errors but I'm not getting the desired output either. Infact, I'm getting no output at all.

The stored procedure is as follows:
CREATE PROCEDURE [dbo].[AllValuations]

@StreetName NVARCHAR(30)

AS
BEGIN

SELECT PropertyRefNo, Unit, Number, StreetName, Area, City, PostCode, PropertyType, PropertyStyle, Bedrooms, FloorLevel, ParkingType, Garden, Valuer,
AppointmentDate, AppointmentTime, AppointmentComments, ValuationPrice, ValuationComments, FeeQuoted, Status, SourceOfEnquiry
FROM tblValuation
WHERE (StreetName LIKE '%'+@StreetName+'%')

END

thanks for your help.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-05 : 10:37:58

Can you show us some sample data, expected result and execute statement on procedure?

Madhivanan

Failing to plan is Planning to fail

ameet.ayare
Starting Member

6 Posts

Posted - 2007-07-05 : 11:20:18

Below is ths sample data (3 records) in the order of the query.

1, 25, Parkhead Loan, Parkhead, Edinburgh, EH11 4SJ, Flat, Upper Flat, 2, 1, On Street, Large Garden, DFA, 29/05/2007 00:00:00, 25/06/2007 14:15:00, NULL, 135000.0000, NULL, 1, 1, PreSale, NULL

2, NULL, 29, Mayfield Terrace, Newington, Edinburgh, EH9 1RY, House, Lower Villa, 4, 0, On Street, Patio, DFA, 29/05/2007 00:00:00, 29/05/2007 16:00:00, NULL, 495000.0000, NULL, 1, 2, Pre Sale, NULL

4, 1, 7, Breakwater, Platinum, Western Harbour, Edinburgh, EH5 5RD, Flat, Duplex, 2, 2, Secure Park, Communal, DFA, 26/03/2007 00:00:00, 26/03/2007 16:00:00, NULL, 349950.0000, NULL, 1, 6, Pre Sale, Internet

Expected Result: I want to be able to search a property by its street name. It should not be necessary for the user to enter the full address of the street, just a few letters or part address. For example, if I enter "park" as a parameter to the query, the result will be the first record.

Execute Statement on procedure: EXECUTE dbo.AllValuations 'parkhead loan'
I tried to give a many different combinations of the parameter but have no output at all. One very interesting thing was: whenever I used LIKE it did not give me an output at all, even when my where clause was: "WHERE StreetName LIKE @StreetName".

I have checked some resources and the solution that you had previously given is exactly right but I'm having no joy. Perhaps, I'm doing something really stupid!!!

ameet.ayare
Starting Member

6 Posts

Posted - 2007-07-05 : 11:35:13

Hi Madhivanan,

I found the exact reason why (I think)!

It did not work because the column's datatype did not match the parameter datatype (atleast now I'm getting the desired output).

Thanks a lot for your help.

Ameet

ameet.ayare
Starting Member

6 Posts

Posted - 2007-07-05 : 11:52:04

I'm now having a new problem!

I wanted to use two parameters for this proc. Area & StreetName, the stored procedure it self is failing on the following code:

ALTER PROCEDURE [dbo].[AllValuations]

@StreetName NVARCHAR(30)
@Area NVARCHAR(30)

AS
BEGIN

SELECT PropertyRefNo, Unit, Number, StreetName, Area, City, PostCode, PropertyType, PropertyStyle, Bedrooms, FloorLevel, ParkingType, Garden, Valuer, AppointmentDate, AppointmentTime, AppointmentComments, ValuationPrice, ValuationComments, FeeQuoted, Status, SourceOfEnquiry

FROM tblValuation

WHERE ((StreetName like '%'+@StreetName+'%') OR (Area like '%'@Area'%'))

END

The error I get is:
Msg 102, Level 15, State 1, Procedure AllValuations, Line 4
Incorrect syntax near '@Area'.
Msg 137, Level 15, State 2, Procedure AllValuations, Line 12
Must declare the scalar variable "@StreetName".

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-05 : 12:00:42

You have missed +

WHERE ((StreetName like '%'+@StreetName+'%') OR (Area like '%'+@Area+'%'))

Madhivanan

Failing to plan is Planning to fail

ameet.ayare
Starting Member

6 Posts

Posted - 2007-07-05 : 12:09:31

Still not working! The same error message. Does it have anything to do with 2 parameters? If I remove @Area parameter definition and the additional OR clause it works but not with 2 parameters!!!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-05 : 12:33:28

ALTER PROCEDURE [dbo].[AllValuations]
(
@StreetName NVARCHAR(30),--you omitted comma here
@Area NVARCHAR(30)
)
AS
BEGIN

SELECT PropertyRefNo, Unit, Number, StreetName, Area, City, PostCode, PropertyType, PropertyStyle, Bedrooms, FloorLevel, ParkingType, Garden, Valuer, AppointmentDate, AppointmentTime, AppointmentComments, ValuationPrice, ValuationComments, FeeQuoted, Status, SourceOfEnquiry

FROM tblValuation

WHERE ((StreetName like '%'+@StreetName+'%') OR (Area like '%'+@Area+'%'))

END

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources