Gripe 3, 4 with Oracle – concat + magic quotes
Concat:
Most of the database queries I run at some point are used to generate HTML and rather than go through the results of the query and add HTML to it in PHP or Ruby code, I like to get this out of the query itself.
So in MySQL my queries often look like this:
select concat(’<a href=pagename.php?varid=”‘, varname, ‘”>’, varid, ‘</a>’) as varlink, …..
However for the same result in Oracle, which only allows two items in the concat function,
this turns into:
select concat(’<a href=pagename.php?varid=”‘, concat(varname, concat(’”>’, concat(varid, ‘</a>’)))) as varlink, …..
If there are multiple such items, the query becomes extremely ugly with too many brackets to keep track of.
Magic Quotes:
My next complaint about Oracle is no magic quotes – string values must be in single quotes, no double quotes allowed.
Related posts:
- Quotes from Blockbuster Customer Service I have more than 100 “Available” movies in my queue...
Related posts brought to you by Yet Another Related Posts Plugin.
I’m not sure why you are using that concat function – its not the Oracle way of doing things – they probably added it to be ANSI compliant or something. Just use the || operator:
select ‘‘ || varid || ‘‘ as varlink …
Also, there is a more sophisticated quoting mechanism which allows you to use single-quotes inside your quoted string without having to “double them up”:
select ‘abc”def’ the_old_way, q’#abc’def#’ the_new_way, q”abc’def” an_alternative_way
from dual
The leading ‘q’ character is mandatory as are the outer single-quotes. However, you have a wide range of choices for the inner character – I have shown it above with a hash character or a second single quote character but you could also use open and close brackets etc. Is this what you mean by magic quotes?
You can use this wherever a string literal is allowed so your above query could become:
select q”” || varid || q”” as varlink …
Yuck – the markup got removed from my last post but I’m sure you can figure out what I typed…
Thanks, something new for me to use