First, sorry for falling off the radar - I personally get annoyed when people post a question then vanish. That said I have been looking at and playing around with...

jaime.simancas 27970 (1/7/2016)

---

Alan.B (1/7/2016)

---

erics44 (1/7/2016)

---

Lynn Pettis (1/7/2016)

---

You have to remember that many times threads go off on tangents, and sometimes the tangents actually impart knowledge that may not have been...

Lynn Pettis (1/7/2016)

---

Sergiy (1/7/2016)

---

Alan.B (1/7/2016)

---

Using a CTE and keeping the code compatible with SQL 2005, we could re-write the code like this:

...

Huge, Huge improvement!!! Irrefutable evidence of the usefulness of...

erics44 (1/7/2016)

---

Lynn Pettis (1/7/2016)

---

You have to remember that many times threads go off on tangents, and sometimes the tangents actually impart knowledge that may not have been provided otherwise.

I don't...

.... and DelimitedSplit8K_LEAD

DECLARE

@pString varchar(8000) = 'xxx,yyy,zzz',

@delimiter char(1) = ',';

SELECT

ItemNumber = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),

...

Eh, I decided to join in the fun and got beat to it (I realized what I posted earlier had CTEs in it)

Using delimitedsplit8K logic... here's one that's not limited...

Sergiy (1/6/2016)

---

Complete version, including non-CTE on-fly Tally table:

DECLARE @pString VARCHAR(8000), @pDelimiter CHAR(1)

SET @pString = 'aaaa,,ccc,d,'

SET @pDelimiter = ','

SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY cteLen.N1),

...

Sergiy (1/6/2016)

---

Lynn Pettis (1/6/2016)

---

Unfortunately you failed to recreate the function as originally coded. You are relying on the existence of a tally table in the database. The original...

<Removed> got hit by the Reply bug

Luis Cazares (1/6/2016)

---

Been following this discussion, but I didn't feel like getting in it.

I believe that over use of CTEs is as bad as the lack of CTEs. I'd rather...

erics44 (1/6/2016)

---

Alan.B (1/6/2016)

---

erics44 (1/6/2016)

---

Alan.B (1/6/2016)

This is just silly. Every MVP and Certified Master whose article and/or book I have read uses CTEs. And I use them and I know how...

erics44 (1/6/2016)

---

Alan.B (1/6/2016)

This is just silly. Every MVP and Certified Master whose article and/or book I have read uses CTEs. And I use them and I know how to design...

Jeff Moden (1/6/2016)

---

Alan.B (1/5/2016)

---

SQLBill (1/5/2016)

---

Lynn Pettis (1/4/2016)

---

SQLBill (1/4/2016)

---

Lynn Pettis (1/4/2016)

---

SQLBill (1/4/2016)

---

I stayed up way longer than normal during the holiday break, but I forced myself to get in the bed...

Sergiy (1/5/2016)

---

Alan.B (1/5/2016)[hrWhat I was demonstrating was in response to the OP's original question and is why someone would use a CTE instead of doing a direct update... especially when...

SQLBill (1/5/2016)

---

Lynn Pettis (1/4/2016)

---

SQLBill (1/4/2016)

---

Lynn Pettis (1/4/2016)

---

SQLBill (1/4/2016)

---

I stayed up way longer than normal during the holiday break, but I forced myself to get in the bed (and sleep) on...