I am trying to create a utility stored proc, that can take a parameter and query a handy table for retrieving some quick info. For this I need to add '%' character before and after the param passed so as to use it in the query with like, as shown below.
Working code - if hardcoded
select some_field from TABLE where nm_field like ('%'+'abc'+'%')
But when I try to use the same with a variable, I get lesser results as compared to above query
declare @nm char(100)
select @nm = 'abc'
select some_field from TABLE where nm_field like ('%'+@abc+'%')
On some investigation, trial and error, the following select shows that '%' is added only before the param, and not at both sides.
declare @name char(20), @other char(20)
select @name = 'abc'
select @other = ('%' + @name + '%')
select @other
Expected Output
%abc%
But actual output
%abc
I am using Sybase ASE-12.5.4
Can you please help me out with what is going wrong here?
Try to change @nm type to varchar as below
declare @nm varchar(100)
select @nm = 'abc'
select some_field from TABLE where nm_field like ('%'+@abc+'%')
Char type has constant size and db fill a gap using spaces
declare @nm char(100)
select @nm = 'abc'
below select
select @nm
will return abc and 97 spaces.