Using SQL variable in like statement

Question

I have done much research on this but just cannot get it right. I am trying to query an audit trail using the below:

declare @batchid char (30)
select @batchid='13857584'  --enter batch id here
declare @occurred int 
select @occurred='5'
print 'Batch is in system, check MRN and encounter. User also noted below'
select a.encounter, a.mrn, a.facility, c.fullname, t.action_desc, a.occurred, a.remark 
from audit..audit_trail a (nolock)
join cabinet..users c (nolock)
on a.userinstanceid=c.userinstanceid
join audit..action_table t (nolock)
on a.action=t.action
where a.REMARK like '%' +  @batchid  + '%'
and OCCURRED > GETDATE()-@occurred
order by a.occurred

for some reason it will never return the results i want (no results). But when i run it specifically for the batch id like below (instead of @batchid)

declare @batchid char (30)
select @batchid='13857584'  --enter batch id here
declare @occurred int 
select @occurred='5'
print 'Batch is in system, check MRN and encounter. User also noted below'
select a.encounter, a.mrn, a.facility, c.fullname, t.action_desc, a.occurred, a.remark 
from audit..audit_trail a (nolock)
join cabinet..users c (nolock)
on a.userinstanceid=c.userinstanceid
join audit..action_table t (nolock)
on a.action=t.action
where a.REMARK like '%' +  '13857584' + '%'
and OCCURRED > GETDATE()-@occurred
order by a.occurred

it works perfectly. I tried adding quotes after and before percentage signs but i just cant get it right. Any help appreciated. SQL 2008

Declare the variable as varchar not char. As char it is padded out with trailing spaces. — Martin Smith
– Martin Smith, Commented May 1, 2014 at 15:08
Just a comment, but you can use set instead of select when you want to set your variable values. set @batchid='13857584' — crthompson
– crthompson, Commented May 1, 2014 at 15:14

Gordon Linoff · Accepted Answer · 2014-05-01 15:11:04Z

5

You care declaring batchid as a char:

declare @batchid char (30)
select @batchid='13857584'  --enter batch id here

In fact, the batchid is being set to something like:

'13857584______________________'

The underscores are intended to show the space character, not an actual underscore.

Try changing it to varchar():

declare @batchid varchar(30);
select @batchid = '13857584';  --enter batch id here

answered May 1, 2014 at 15:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1879840 Over a year ago

you my friend are awesome! thank you so much !!! so simple didnt see it. Answer check in 10 minutes when it allows me :)

Collectives™ on Stack Overflow

Using SQL variable in like statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related