opc.three (5/23/2011)
Closed "as Won't Fix" with the reason being "Backward Compatibility Issues"...Booooo!
I guess we can officially call STUFF a legacy function 🙂
Anyone have insight into what the "ANSI SQL OVERLAY function" is?
From Umachandar's response:
We might consider adding the ANSI SQL OVERLAY function in the future based on more customer feedback...
OK, the wording through me off...I thought Umachandar was alluding to a new server option that could be SET, but in fact OVERLAY is a scalar function defined by in the ANSI SQL Standard that does not yet exist in SQL Server. OVERLAY would do the same work as STUFF but could be implemented to properly handle NULL inputs. This was the only article I could find that defined it:
http://users.atw.hu/sqlnut/sqlnut2-chp-4-sect-4.html#sqlnut2-CHP-4-TABLE-9
In case the article moves:
OVERLAY
The OVERYLAY function embeds one string into another and returns the result.
SQL2003 Syntax
OVERLAY(string PLACING embedded_string FROM start[FOR length])
If any of the inputs are NULL, the OVERLAY function returns a NULL. The embedded_string replaces the length characters in string starting at character position start. If the length is not specified, then the embedded_string will replace all characters after start in string.
DB2, MySQL, Oracle, and SQL Server
These platforms do not have support for the OVERLAY function. You can simulate the OVERLAY function on these platforms by using a combination of SUBSTRING and the concatenation operator.
PostgreSQL
PostgreSQL supports the ANSI standard for OVERLAY.
Examples
This is an example of how to use the OVERLAY function:
/* SQL2003 and PostgreSQL */
SELECT OVERLAY('DONALD DUCK' PLACING 'TRUMP' FROM 8) FROM NAMES;'DONALD TRUMP'
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato