Re: [Tcl-tdbc] SQL LIKE '%test%' problem

[Anton <wa...@to...>]

Thanks to everybody.

I'm not a mysql guru, I did not know that LIKE uses plain search and check all table. I use relatively small tables so
it does not affect on speed for me. Whatever I do not know other much more fast kind of substring search.

  set stmt [ mh prepare {SELECT * FROM radcheck WHERE username LIKE CONCAT('%',:ffff,'%')} ];

This is a working construction.

And yes I heard that MySQL has problems with SQL standards. In any way thanks for idea with concatenation :).



В Wed, 2 Jan 2013 19:03:19 +0100
to...@tu... пишет:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> On Wed, Jan 02, 2013 at 02:48:21PM +0000, Donal K. Fellows wrote:
> > On 02/01/2013 08:45, Anton wrote:
> >> What is the right way to insert variable in " LIKE '%$var%' " style in 
> >> tdbc::mysql prepare ?
> >>
> >
> > Parameters only substitute for whole words (because they don't use
> > string substitution under the covers, but rather injection as
> > independent values into the *compiled* version of the query on the
> > server side) so you need to use something like this:
> >
> >   SELECT * FROM radcheck WHERE username LIKE '%' || :ffff || '%'
> >
> > (The “||” is the standard SQL string concatenation operator.)
> 
> NOTE: the last MySQL I had contact with didn't know about ||. You'd have
> to use concat there, like so:
> 
> >   SELECT * FROM radcheck WHERE username LIKE concat('%', :ffff ||, '%')
> 
> or even
> 
> >   SELECT * FROM radcheck WHERE username LIKE concat('%', concat( :ffff, '%'))
> 
> (I don't remember whether MySQL's concat takes a variable number of
> args).
> 
> Unless tdbc::mysql is smart and understands the || operator: then you
> may ignore my talk above completely, that is.
> 
> On PotgreSQL, || would work as stated above.
> 
> Regards
> - -- tomás 
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.6 (GNU/Linux)
> 
> iD8DBQFQ5HZnBcgs9XrR2kYRAkScAJ0T8oX3p8+R/L4WR79BrzAu6+Ya3gCfcCAV
> RKEzjjQxknEECD5VxWff+Bw=
> =SbOf
> -----END PGP SIGNATURE-----