Using @ variables with LIKE,CONCAT

--0016e659f204cc3d1304a304baf9
Content-Type: text/plain; charset=ISO-8859-1

This used to work fine in Mysql 4.3, but no longer works in 5.5.8:

set @txt='needle';
select * from table where field like CONCAT('%',@txt,'%');

--returns the null set. If I substitute like this:

select * from table where field like '%needle%';

it works perfectly (and as it did in 4.x).

How can I get this to work in 5.5.x?

Thanks,

-Hank

--0016e659f204cc3d1304a304baf9--

Re: Using @ variables with LIKE,CONCAT

--0050450155a1739da404a3052446
Content-Type: text/plain; charset=ISO-8859-1

Hi, I just tried this on a schema I had laying about and it worked fine:

mysql> SET @dude='pilgrim';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT namefield FROM mytable WHERE namefield LIKE
CONCAT('%',@dude,'%');
+-----------------------------------------------+
| name |
+-----------------------------------------------+
| Blood Elf Pilgrim |
| Blood Elf Pilgrim |
| Draenei Pilgrim |
| High Elf Pilgrim |
| Pilgrim Gal'ressa |
| Recovering Pilgrim |
| Wounded Blood Elf Pilgrim |
| Young Pilgrim |
+-----------------------------------------------+

I am running 5.5.6 x64 on Mac OS X.

Rich


On 11 May 2011 20:03, Hank wrote:

> This used to work fine in Mysql 4.3, but no longer works in 5.5.8:
>
> set @txt='needle';
> select * from table where field like CONCAT('%',@txt,'%');
>
> --returns the null set. If I substitute like this:
>
> select * from table where field like '%needle%';
>
> it works perfectly (and as it did in 4.x).
>
> How can I get this to work in 5.5.x?
>
> Thanks,
>
> -Hank
>



--
*Richard Bensley*
*Database Administrator*
*
*
richard.bensley@photobox.com
skype: richardbensley
Mobile: 07540878285

--0050450155a1739da404a3052446--