Re: Need substitutes for CONCAT(), NVL2(), % wildcard

From: <fitzjarrell_at_cox.net>
Date: Fri, 14 Sep 2007 06:54:46 -0700
Message-ID: <1189778086.867684.6580@o80g2000hse.googlegroups.com>

On Sep 14, 4:56 am, seannakas..._at_yahoo.com wrote:
> We have an application that runs with either Oracle or MSDE and I'm
> looking for substitutes for the following commands that will be
> compatible on both databases: CONCAT(), NVL2(), % wildcard. I believe
> they only currently are supported in Oracle.

The % wildcard is standard fare for DBMS engines (of course Access doesn't use it, but I don't consider Access a DBMS). CONCAT() (also ||) is replaced by '+', which concatenates strings (and it's also the numeric addition operator, so it can be confusing). NVL2 is an Oracleonly construct (the others are not), and is best replaced by a CASE statement:

case

     when <colname> is null then <expression>
     else <expression>

end

Of course writing 'database-independent' applications is a certain source of problems from a performance standpoint, as well as a scalability standpoint. I would suggest you write two versions of the product and leverage the inherent capabilities of each, but I know that recommendation will fall upon deaf ears.

All of this information I've presented is available in SQL Server Books On-line, the SQL Server/MSDE documentation, available for absolutely no charge from www.microsoft.com/sql. You should download a copy for personal reference.

David Fitzjarrell Received on Fri Sep 14 2007 - 08:54:46 CDT