I was hoping to have it as 1 select statement as I have to join it to other data.  I do appreciate what you showed me however, as it...

;WITH CTE AS
(
    SELECT 'urn:ADL:CC_LB_SF_EN_D_C_%5BLB_LS_TCD%5D_%5BLB_LS_CotCF%5D_28_1' Original
),
CTE1 AS
(
    SELECT *,
           REPLACE(REPLACE(CTE.Original,'%5B','['),'%5D',']') SquareBrackets
...

Seeks show efficient use of the index.

The optimiser would rather do a scan of a clustered instead of a scan on an index if it thinks a lot of rows...

Ok, it's normal convention to refer to rows of code as lines of code. What you actually meant was columns as each column...

Do you mean rows or columns?

If you mean columns then you don't actually...

If you right-click the table in SSMS then script the table as INSERT, then do the same again and script the tables as SELECT. Paste this SELECT statement at the...

Is the ID column an IDENTITY column?

The accessor of the cte always delivers the next value in the recursion.  #2 returns '2019-10-13' which is the next value after the inequality inside the cte.  It is...

I don't have access to a 2008 server, but maybe you could try it with...

Thanks Jonathan, nice function. Which versions of SQL Server it will work with? It doesn't seem to work on 2008R2. I replaced IIF with CASE but now getting this:

"WHERE crc.[value] < parms.end_dt" was not necessary at all so I deleted it.  Your function obviated that necessity.  I wrote that but then I pasted it in with the...

I'd be interested to know if the SSIS transforms perform any better or worse than well-written SQL scripts.

Thank you Jeff, that's a great compliment coming from someone as experienced as you.

Hi scdecade,

Thanks for the feedback, I'm glad you've found a use for the function.

I didn't use "drop if exists" as this is a feature that was only introduced in SQL...

So when dealing with data transformations at tables with 40-50GByte in size: is it recommended to use SSIS interal operators at all or should I go always for DB...