Using 'like' query with data containing wildcard characters requiring ESCAPE?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Using 'like' query with data containing wildcard characters requiring ESCAPE?

Kenneth Ölwing
Hi,

I happen to have text data in my table that has the '_' (underscore)
character, and I wanted to do a like search.

So to find all rows with '_' I did:

     my @rows = $rs->search( { data => { like => '%_%' } } );

and running with DBIC_TRACE=1, I see:

     SELECT me.data FROM test me WHERE ( data LIKE ? ): '%_%'

I didn't get the result I was expecting. And duh, it dawned on me that
'_' is itself a wildcard char...

After realising that, I researched and found out that I could write
something like this (a direct query):

     SELECT * FROM test WHERE (data LIKE '%\_%' ESCAPE '\');

Being a beginner with DBIx::Class, I may not have found the right place
to look, but so far no luck...can I achieve this effect with the Perl
code, and if so how? I'm using SQLite for the moment and Perl 5.16 and
latest DBIx::Class.

TIA, any help appreciated.

ken1


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4716 / Virus Database: 3986/7876 - Release Date: 07/18/14


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: Using 'like' query with data containing wildcard characters requiring ESCAPE?

Bob MacCallum
Hi Ken,

This works for me - did you try the obvious?

$resultset->search({ name => { 'like' => '%\_%' } } )

cheers,
Bob





On Fri, Jul 18, 2014 at 7:24 PM, Kenneth Ölwing <[hidden email]> wrote:
Hi,

I happen to have text data in my table that has the '_' (underscore) character, and I wanted to do a like search.

So to find all rows with '_' I did:

    my @rows = $rs->search( { data => { like => '%_%' } } );

and running with DBIC_TRACE=1, I see:

    SELECT me.data FROM test me WHERE ( data LIKE ? ): '%_%'

I didn't get the result I was expecting. And duh, it dawned on me that '_' is itself a wildcard char...

After realising that, I researched and found out that I could write something like this (a direct query):

    SELECT * FROM test WHERE (data LIKE '%\_%' ESCAPE '\');

Being a beginner with DBIx::Class, I may not have found the right place to look, but so far no luck...can I achieve this effect with the Perl code, and if so how? I'm using SQLite for the moment and Perl 5.16 and latest DBIx::Class.

TIA, any help appreciated.

ken1


-----
No virus found in this message.
Checked by AVG - www.avg.com
Version: 2014.0.4716 / Virus Database: 3986/7876 - Release Date: 07/18/14


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: Using 'like' query with data containing wildcard characters requiring ESCAPE?

Dagfinn Ilmari Mannsåker
Kenneth Ölwing <[hidden email]> writes:

> After some looksee I figured out that I can do a naughty literal thing:
>
> ###
> ...
> my @someRows = $rs->search( { data => { like => \q('%\_%' ESCAPE '\') } });
> ...
> ###
>
> That does print the expected two rows, but it seems a bit messay to have
> to go to literal sql to get it done, especially since this should be
> generic code that gets the actual search value from user input and thus
> should work with bind data which apparently works with ESCAPE clause as
> intended:
>
>     SELECT * FROM test WHERE (data LIKE ? ESCAPE '\'); # and bind the
> data, works in sqlitestudio
>
> So even if a literal piece would be required, I don't at the moment see
> how I can do it so bind is used in the Perl code...?

You need to use an arrayrefref to combine literal SQL and bind
parameters:

    $rs->search({ data => { like => \[q{? ESCAPE '\'}, '%\_%'] } });

The SQL::Abstract documentation has more information:

https://metacpan.org/pod/SQL::Abstract#Literal-SQL-with-placeholders-and-bind-values-subqueries

And DBIx::Class::ResultSet documents DBIC-specific bind parameter
details:

https://metacpan.org/pod/DBIx::Class::ResultSet#DBIC-BIND-VALUES 

>
> ken1
>
> On 2014-07-21 13:22, Bob MacCallum
>  wrote:
>> Hi Ken,
>>
>> This works for me - did you try the obvious?
>>
>> $resultset->search({ name => { 'like' => '%\_%' } } )
>>
>> cheers,
>> Bob
>>
>>
>>
>>
>>
>> On Fri, Jul 18, 2014 at 7:24 PM, Kenneth Ölwing <[hidden email]
>> <mailto:[hidden email]>> wrote:
>>
>>     Hi,
>>
>>     I happen to have text data in my table that has the '_'
>>     (underscore) character, and I wanted to do a like search.
>>
>>     So to find all rows with '_' I did:
>>
>>         my @rows = $rs->search( { data => { like => '%_%' } } );
>>
>>     and running with DBIC_TRACE=1, I see:
>>
>>         SELECT me.data FROM test me WHERE ( data LIKE ? ): '%_%'
>>
>>     I didn't get the result I was expecting. And duh, it dawned on me
>>     that '_' is itself a wildcard char...
>>
>>     After realising that, I researched and found out that I could
>>     write something like this (a direct query):
>>
>>         SELECT * FROM test WHERE (data LIKE '%\_%' ESCAPE '\');
>>
>>     Being a beginner with DBIx::Class, I may not have found the right
>>     place to look, but so far no luck...can I achieve this effect with
>>     the Perl code, and if so how? I'm using SQLite for the moment and
>>     Perl 5.16 and latest DBIx::Class.
>>
>>     TIA, any help appreciated.
>>
>>     ken1
>>
>>
>>     -----
>>     No virus found in this message.
>>     Checked by AVG - www.avg.com <http://www.avg.com>
>>     Version: 2014.0.4716 / Virus Database: 3986/7876 - Release Date:
>>     07/18/14
>>
>>
>>     _______________________________________________
>>     List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>>     IRC: irc.perl.org#dbix-class <http://irc.perl.org#dbix-class>
>>     SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>>     Searchable Archive:
>>     http://www.grokbase.com/group/dbix-class@...
>>
>>
>>
>>
>> _______________________________________________
>> List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
>> IRC: irc.perl.org#dbix-class
>> SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
>> Searchable Archive: http://www.grokbase.com/group/dbix-class@...
>>
>>
>> No virus found in this message.
>> Checked by AVG - www.avg.com <http://www.avg.com>
>> Version: 2014.0.4716 / Virus Database: 3986/7891 - Release Date: 07/21/14
>>
>
>
>
>
> -----
> No virus found in this message.
> Checked by AVG - www.avg.com
> Version: 2014.0.4716 / Virus Database: 3986/7894 - Release Date: 07/21/14

--
"I use RMS as a guide in the same way that a boat captain would use
 a lighthouse.  It's good to know where it is, but you generally
 don't want to find yourself in the same spot." - Tollef Fog Heen


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...
Reply | Threaded
Open this post in threaded view
|

Re: Using 'like' query with data containing wildcard characters requiring ESCAPE?

Kenneth Ölwing
On 23.07.2014 01:01, [hidden email] wrote:

> You need to use an arrayrefref to combine literal SQL and bind
> parameters:
>
> $rs->search({ data => { like => [q{? ESCAPE ''}, '%_%'] } });
>
> The SQL::Abstract documentation has more information:
>
> https://metacpan.org/pod/SQL
>
> [6]::Abstract#Literal-SQL-with-placeholders-and-bind-values-subqueries
>
> And DBIx::Class::ResultSet documents DBIC-specific bind parameter
> details:
>
> https://metacpan.org/pod/DBIx [7]::Class::ResultSet#DBIC-BIND-VALUES

Awesome, thanks a lot. Given that the info *is* there I might have
figured it out eventually, but I wouldn't bet on it, there's quite a bit
in this area I have yet to wrap my head around :-)


ken1


_______________________________________________
List: http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbix-class
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/DBIx-Class/
Searchable Archive: http://www.grokbase.com/group/dbix-class@...