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

SQL Server Development (2000)

LIKE Keyword

Author

Topic

macca
Posting Yak Master

146 Posts

Posted - 2008-03-27 : 11:45:45

I am doing a search where I search on a field, the field is an Int. Here is the sql I use:

select * from table
where field LIKE '%' + 555666 + '%'

I keep getting the following error:
Syntax error converting the varchar value '%' to a column of data type int.

Anyone any ideas?
macca

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2008-03-27 : 11:47:41

select * from table
where field LIKE '%' + Convert(Varchar,555666)+ '%'

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-27 : 11:49:15

select * from table
where field LIKE '%555666%'

E 12°55'05.25"
N 56°04'39.16"

macca
Posting Yak Master

146 Posts

Posted - 2008-03-27 : 12:00:19

Thanks guys both your suggestions worked.
Another question how would I get that to work in a Stored Procedure where @number would be substituted for the number 555666 like this:

select * from table
where field LIKE '%' + @number + '%'

Or

select * from table
where field LIKE '%@number%'

Would either the above work?
macca

macca
Posting Yak Master

146 Posts

Posted - 2008-03-27 : 12:24:13

If I use these in a stored procedure it does not return the records, here is my stored procedure:

CREATE PROCEDURE sproc_FindByPlanRef

(
@pref varchar(10)
)

AS

SELECT O.OrderNum, O.PlanRef, O.Title, O.OrderSigned, Os.StatusName

FROM OrderStatus AS Os INNER JOIN Orders As O ON Os.StatusID = O.StatusID

WHERE O.PlanRef LIKE '%' + Convert(Varchar, @pref)+ '%'
GO

Any ideas?
macca

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-27 : 12:57:21

since @pref already is varchar, there is no need to convert it again.

CREATE PROCEDURE sproc_FindByPlanRef

(
@pref varchar(10)
)
AS

SELECT O.OrderNum, O.PlanRef, O.Title, O.OrderSigned, Os.StatusName
FROM OrderStatus AS Os INNER JOIN Orders As O ON Os.StatusID = O.StatusID
WHERE O.PlanRef LIKE '%' + @pref + '%'
GO

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-27 : 15:58:03

quote:
Originally posted by macca

If I use these in a stored procedure it does not return the records, here is my stored procedure:

CREATE PROCEDURE sproc_FindByPlanRef

(
@pref varchar(10)
)

AS

SELECT O.OrderNum, O.PlanRef, O.Title, O.OrderSigned, Os.StatusName

FROM OrderStatus AS Os INNER JOIN Orders As O ON Os.StatusID = O.StatusID

WHERE O.PlanRef LIKE '%' + Convert(Varchar, @pref)+ '%'
GO

Any ideas?
macca

Make sure you always specify a length while casting to varchar type.

macca
Posting Yak Master

146 Posts

Posted - 2008-03-28 : 06:11:22

Thanks dinakar and peso, I have also tried this and it still brings back a blank.
When I run the stored procedure in sql query analyzer and I add in value for @number it runs fine and returns the records but when I run the stored procedure in my application it won't return anything. The variable is passed from the application to the stored procedure fine but just doesn't work.

Any ideas?
macca

macca
Posting Yak Master

146 Posts

Posted - 2008-03-28 : 06:13:30

Here is my actual stored procedure:

CREATE PROCEDURE sproc_FindByPlanRef

(
@pref varchar(20)
)

AS

SELECT O.OrderNum, O.PlanRef, O.Title, O.OrderSigned, Os.StatusName

FROM OrderStatus AS Os INNER JOIN Orders As O ON Os.StatusID = O.StatusID

WHERE O.PlanRef LIKE '%' + Convert(Varchar(20), @pref)+ '%'
GO

O.PlanRef field is varchar 20 also.

macca

Imukai
Starting Member

29 Posts

Posted - 2008-03-28 : 07:09:55

Do you know your application is passing the value 100% definately? Have you run a SQL Trace while the app queries the db to see the exact statement being sent over?

If it works in QA but not from your app, sounds like either the app isn't sending over what you think it's sending, or maybe the app isn't handling the returned recordset properly.

I'd say start a trace, run the app, if the SQL statement looks ok in trace, copy/paste that same query into QA to see what is being sent back to the app - and then diagnose it from the app's perspective if necessary.

macca
Posting Yak Master

146 Posts

Posted - 2008-03-28 : 08:22:21

Guys thank you all very much.

I know have that working.

macca

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources