• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

using like with % and variable in the select where clause

Hi,

I'm calling a stored procedure and passing a parameter - @str

I want to include % in a like statement in a select as

select description
from   table
where description like %@str%

I've tried '%'+@str+'%'  but doesnt work.  

Any ideas

Thx, Pat
0
pmkenna
Asked:
pmkenna
1 Solution
 
Kevin3NFCommented:
Set @str = '%' + @str + '%'

select companyname
from   customers
where companyname like @str
0
 
adatheladCommented:
select description
from   table
where description like '%' + @str + '%'

Should work as it's perfectly valid.
What datatype is @str?
i.e.
if it is a CHAR(10) then it may be padded with blank spaces and hence not finding results in which case try:


select description
from   table
where description like '%' + LTRIM(RTRIM(@str)) + '%'
0
 
lluthienCommented:
make sure that the LENGTH of your nvarchar is okay.

i just tested this situation..
declare @str as nvarchar()
set @str = 'aa'
set @str= '%' + @str + '%%'
select @str

gives '%'

declare @str as nvarchar(10)
set @str = 'aa'
set @str= '%' + @str + '%%'
select @str

gives '%aa%'


hth
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lluthienCommented:
doubled the %'s  

unintentionally.
0
 
Melih SARICAIT ManagerCommented:

declare @str as varchar

and

before using it in the select statement

set @Str = '%'+@Str+'%'

and then use it in ur Select statement

0
 
pmkennaAuthor Commented:
Ok Folks, still not working...

Description is an nvarchar field

Using select Description from table where contains(*, @str)      is working
Using select Description from table where like '%ball%'             will also work

Using

create procedure test
    @Str nvarchar(255)
as
set nocount on

Set @Str = '%'+@Str+'%'

select Description from table where Description like @Str       will not work?
0
 
Anthony PerkinsCommented:
>>select Description from table where Description like @Str       will not work? <<
Sure it will.  What is the problem?

But you should use CONTAINS if you can, it will be a lot faster than LIKE and it is a lot more powerful.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now