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.
No related posts.
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