Parameters and 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

General SQL Server Forums

New to SQL Server Programming

Parameters and LIKE

Author

Topic

virtcert
Starting Member

6 Posts

Posted - 2006-04-17 : 10:19:09

I'm trying to do this:

ALTER PROCEDURE dbo.SkillSearch
(
@skillname char(255)
)
AS
SET NOCOUNT ON
SELECT * FROM Skill WHERE SkillName LIKE @skillname

However, when I run dbo.skillsearch 'ph%'

I get an empty set, while dbo.skillsearch 'php' returns the results expected.

I am therefore assuming that I can't use a parameter for a LIKE clause with any wildcards?

Is there any way around this other than manually building the SQL statement in the SP and then executing it? I'd obviously prefer to not have to do it that way for all the SQL Injections and related reasons.

Wouldn't this also kill my query optimization benefits, manually building the statement each time?

Thanks,

- Brian

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-17 : 10:23:01

[code]ALTER PROCEDURE dbo.SkillSearch
(
@skillname varchar(255)
)
AS
SET NOCOUNT ON
SELECT * FROM Skill WHERE SkillName LIKE @skillname[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 10:23:56

Try

SELECT * FROM Skill WHERE SkillName LIKE @skillname+'%'

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-17 : 10:26:42

value for @skillname='ph'

where skillname like @skillname + '%'

instead of like you can use patindex

where patindex(@skillsearch +'%',skillname)>0

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-17 : 10:26:51

when you use char(255)
you are searching with like

'ph%               '

with 252 spaces behind the string. ph% which will result in no match

virtcert
Starting Member

6 Posts

Posted - 2006-04-17 : 10:30:38

quote:

Originally posted by khtan

ALTER PROCEDURE dbo.SkillSearch
(
@skillname varchar(255)
)
AS
SET NOCOUNT ON
SELECT * FROM Skill WHERE SkillName LIKE @skillname

Thank you! I knew it had to be something dumb and simple. Those are always the hardest to find! :-)

- Brian

virtcert
Starting Member

6 Posts

Posted - 2006-04-17 : 10:33:52

quote:
Originally posted by jen

value for @skillname='ph'

where skillname like @skillname + '%'

instead of like you can use patindex

where patindex(@skillsearch +'%',skillname)>0

Is there an advantage to using patindex()?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 10:34:09

quote:
Originally posted by khtan

when you use char(255)
you are searching with like
'ph% '
with 252 spaces behind the string. ph% which will result in no match

KH



declare @t table(data char(255))
insert into @t 
	select 'ph' union all
	select 'test'

declare @data char(255)
set @data='ph'

select data from @t
where data like @data+'%'

Madhivanan

Failing to plan is Planning to fail

virtcert
Starting Member

6 Posts

Posted - 2006-04-17 : 10:37:58

quote:
Originally posted by khtan

when you use char(255)
you are searching with like
'ph% '
with 252 spaces behind the string. ph% which will result in no match

KH

I understand this. Why, then, does this work:

ALTER PROCEDURE dbo.SkillSearch
(
@skillname char(25)
)
AS
SET NOCOUNT ON
SELECT * FROM Skill WHERE SkillName = @skillname

dbo.skillsearch 'php'

This returns the appropriate record. However, why does it work, when I'm comparing char(25) to a varchar(255) field for equality. Are the trailing spaces not an issue? Why not now?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 10:42:51

It is because trailing spaces are omitted when comparing strings

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-17 : 10:43:30

[code]declare @t table(data char(255))
insert into @t
select 'pha' union all
select 'test'

declare @data char(255)
set @data='ph'

select data from @t
where data like @data+'%'

data
-------------------
(0 row(s) affected)[/code]

virtcert
Starting Member

6 Posts

Posted - 2006-04-17 : 10:49:52

Thanks, madhivanan!

Is there anything wrong with doing it this way?



declare @t table(data varchar(255))
insert into @t 
	select 'php' union all
	select 'test'

declare @data varchar(255)
set @data='ph%'

select data from @t
where data like @data+'%'

virtcert
Starting Member

6 Posts

Posted - 2006-04-17 : 10:52:30

quote:
Originally posted by madhivanan

It is because trailing spaces are omitted when comparing strings

But not what matching with LIKE? (ie: 'ph%252spaces')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-04-17 : 10:59:57

quote:

Originally posted by khtan

declare @t table(data char(255))
insert into @t 
	select 'pha' union all
	select 'test'

declare @data char(255)
set @data='ph'

select data from @t
where data like @data+'%'

data
-------------------
(0 row(s) affected)

Good catch Tan

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources