ChrisM@Work (6/22/2012)

---

dwain.c (6/22/2012)

---

...The only thing I don't like about your version, and I'm really splitting hairs here, is the hardcoding of the 4 on the LEFT. If you use...

L' Eomot Inversé (6/20/2012)

---

The thread seems very quiet. I've been away three days and there are only 62 new posts on it.

But I may not find...

WayneS (6/18/2012)

---

Ladies, Gentlemen, and Threadizens of all shapes and sizes! Come one, come all for the latest news of the day!

Me, your humble dba, from out of the blue, happened...

Like I said, great work Chris! And thanks for taking the time to look closer.

Now if we can just figure out a way to apply this to the bin...

Not true! You could use my version in a FUNCTION....

After multiple runs, I am starting to see an improvement using your modification over the original! Great work Chris!

When I posted the article, Jeff took a shot at beating...

Note that removing the check column as you have done does get you about a 10% speed improvement.

If you were right though, does that mean my version is 3125x faster...

I see you also removed CHARINDEX. However I can't verify your results. I get this running with n<=2 in the recursive leg:

-- Original

(115681 row(s) affected)

SQL Server Execution...

ChrisM@Work (6/22/2012)

---

dwain.c (6/21/2012)

---

Wow Chris! ...

You're most welcome.

BTW. My edits were caused by the fact that 'ammendment' is mispelled in your sample data. When I constructed the initial query I spelled it correctly...

That might actually be a little easier (grabbing text to left of first invalid character).

Unfortunately, I'm not an expert on PATINDEX. And an initial search didn't turn up a...

Here's a tested solution including the test harness to validate that it seemed to be producing random results.

CREATE TABLE #tblLottery

(

customerId...

You can take a look at this script: http://www.sqlservercentral.com/scripts/Identify+illegal+characters/89934/

It identifies "bad" characters in a column of data - you'll need to define what's "good" (the script assumes alphanumerics are good).

Using...

The query you want should look something like this:

DECLARE @t TABLE (ID INT, StartDt DATETIME, EndDt DATETIME, CodeVal VARCHAR(3))

INSERT INTO @t

SELECT 11, '2012-01-01', '2012-01-05', 'AAA'

UNION ALL SELECT 11, '2012-01-03', '2012-01-07',...

We hear that on the forum a lot (not saying it is an invalid response, just saying). That's...