Re: SQL LIKE and Binding Variables

Replies are listed 'Best First'.
Re^2: SQL LIKE and Binding Variables by Your Mother (Archbishop) on Nov 03, 2009 at 18:26 UTC
I like that and didn't realize you could do it. Do you know if it's portable? Pg, mysql, sqlite, etc?	[reply]
Re^3: SQL LIKE and Binding Variables by erix (Prior) on Nov 03, 2009 at 19:06 UTC
For postgres, the string concatenator would have to be '\|\|' instead of '+'. (A quick test seems to indicate SQLite wants '\|\|' as well, which makes sense as that is the standard SQL concatenator) see (maybe) also: dbi, placeholder and postgres regular expression problem hth	[reply]
Re^2: SQL LIKE and Binding Variables by Mad_Mac (Beadle) on Nov 06, 2009 at 17:07 UTC
Ok, thanks. If I combine the suggestions from Jenda and erix it seems to work pretty well. `my @vals =("artist1", "artist2"); my $sth = $db->prepare(q{SELECT * FROM Songs WHERE Songs.Artist LIKE ' +%'\|\|?\|\|'%' OR Songs.Artist LIKE '%'\|\|?\|\|'%'});# suggestion from Jenda my $results = $sth->execute( @vals ) or die("Failed to Execute SQL");` [download] I'm now struggling to work out the syntax to combine the placeholder `'%'\|\|?\\'?'` with a `join` and `map` statement that I can pass a an array of values. Does anyone know the trick to get this syntax right? If it matters - I am running ActivePerl on Vista X64 and using SQLite for my DB back end.	[reply] [d/l] [select]
Re^3: SQL LIKE and Binding Variables by Jenda (Abbot) on Nov 06, 2009 at 17:42 UTC
Each placeholder can accept only one value so you have to build your SQL statement with the right number of question marks. Or (if your database supports table valued functions) use something like this to split a single string on the database side. (I doubt things like this are supported by SQLite, but I may be mistaken.) Jenda Enoch was right! Enjoy the last years of Rome.	[reply]
Re^4: SQL LIKE and Binding Variables by Mad_Mac (Beadle) on Nov 06, 2009 at 22:40 UTC
Oh, wait ... got it. `my $sql = q{SELECT * FROM Songs WHERE Songs.Artist LIKE '%'\|\|} .join(q +{\|\|'%' OR Songs.Artist LIKE '%'\|\|},map {"?"} @vals).q{\|\|'%'};` [download] Thanks to everyone for you patience and suggestions!	[reply] [d/l]
Re^4: SQL LIKE and Binding Variables by Mad_Mac (Beadle) on Nov 06, 2009 at 22:23 UTC
Right. I understand about needing a placeholder for each value. I know how to do it with most other SQL operators, such as `IN`: `# could be any number of artists my @vals =("Artist1", "Artist2") # And now I build the SQL statement with placeholders based on values my $sql = 'SELECT * FROM Songs WHERE Songs.Artist IN (' .join (", ", m +ap {"?"} @vals) .')';` [download] What's got me stumped is the syntax do do something like the `.join (", ", map {"?"} @vals) .` part of the statement, when using a `LIKE` operator and wrapping it `%` wildcards. Thanks again for your help.	[reply] [d/l] [select]