Gripe 3, 4 with Oracle – concat + magic quotes


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.

3 thoughts on “Gripe 3, 4 with Oracle – concat + magic quotes

  1. Alastair says:

    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 …

  2. Alastair says:

    Yuck – the markup got removed from my last post but I’m sure you can figure out what I typed…

  3. Sidd says:

    Thanks, something new for me to use 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *