You are splitting each string 3 times, once for each UNION - exactly the same way each time. Pre-process the string splitting: take the results from the splitting subqueries...

If I understand the problem correctly, try adding this to the outer part of your main query (copied from your subquery):

and (OH.ConDate is not null or OH.ConDate <> '')

Oh, and...

You refer to a 'derived column' - you are looking for a way to define a very complex computed column (at the table level) is this correct?

Are you perhaps looking for a view solution? Views are a great way to encapsulate reusable logic, but from a set-based perspective.

Additionally, you may want to investigate user-defined functions,...

Just a stab in the dark - what data type is the column being cast to? If it's text (not varchar or char), the left function won't work.

Seems to me that this could be leveraged as a home-grown replication solution between non-homogeneous databases. Just need an application at either end to ship-and-apply the changes.

You could use OPENROWSET, but yeah, if you are having performance issues it is probably due to joins on tables between servers.

If the remote set of data is relatively small,...

Keep in mind that the RAND() function is only pseudo-random. If you need truly random numbers, I'd go with another technique - perhaps utilizing GUIDs?

Edit: Jeff beat me...

I suppose one could just put a check constraint on the source field based on the first part of case statement above. However, that probably wouldn't make any friends...

Right, just a simple case statement like this (I'm sure this probably has plenty of holes, but it's not a half-bad place to start)

Select Case When Len(InputDate) <> 8 Then...

What would be the benefit of converting the UDF to a CLR? In my mind, I would just use a transform on the field(s) at load-time using the UDF...

I actually designed an app that worked in a similar way. The application would read the sql out of a (developer maintained) table and present the results in the...

From Chris Hays's Reporting Services Sleazy Hacks Weblog:

http://blogs.msdn.com/b/chrishays/archive/2004/07/27/allparametervalue.aspx

You noted that you were toying with the idea using SSRS. Had you considered going this route and providing the end users access to build their own reports using...

This meets the requirements, but why you'd need something like this is beyond me. Dynamic sql, use at your own risk...

CREATE PROCEDURE USP_CreateDatabase (

@DBName varchar(50)

) AS

BEGIN

...