Generate SQL Query, SQL::Abstract?

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

First poted on reddit, can't link here.

In a database I have, various field. The fields contain search criteria for that respective field e.g.

FOO
FOO|BAR|BAZ
*TEST*|*BLUE*
[download]

The | character here means 'or' in SQL. So if the column here was called PROD_TITLE, the SQL where clause for above would be:

where PROD_TITLE = 'FOO' 
where PROD_TITLE in ('FOO','BAR','BAZ')
where PROD_TITLE like '*TEST* or PROD_TITLE like '*BLUE'
[download]

To complicate things further I have a specific column for free search data, containing key/value pairs (like URL params):

&BAZ=TEST
&BAZ=*TEST*
&ORDERDATE=20200101:2020130
&BAZ=TEST*&ORDERDATE=20200101:2020130
[download]

I looked at SQL::Abstract::FromQuery but I don't have this data as a URL param. Is there a clever perl way I can use either SQL::Abstract or SQL::Abstract form to generate a SQL query catering for all where clauses?

Comment on Generate SQL Query, SQL::Abstract? Select or Download Code

Replies are listed 'Best First'.
Re: Generate SQL Query, SQL::Abstract? by haukex (Archbishop) on Mar 16, 2020 at 12:12 UTC
It seems like you've got a custom format that you won't be able to use directly with SQL::Abstract, or SQL::Abstract::FromQuery and so on. That means you'll have to transform your input data anyway, whether you transform it to Perl data structures for use in those modules, or you transform it directly to SQL strings. If you do happen to choose the latter, then remember to always use DBI's `quote`, `quote_identifier`, and placeholders!	[reply] [d/l] [select]
Re: Generate SQL Query, SQL::Abstract? by choroba (Cardinal) on Mar 16, 2020 at 17:17 UTC
The link to Reddit. `map{substr$_->[0],$_->[1]\|\|0,1}[\\|\|{},3],[[]],[ref qr-1,-,-1],[{}],[sub{}^ARGV,3]`	[reply] [d/l]