mtassin (12/17/2012)

---

murthyvs (12/16/2012)

---

Hi dwain.c - I tried changing VARCHAR(8000) to VARCHAR(MAX). Ran the query with new pattern [0-9A-Za-z]. My input string is a very long string.

sample:

3038469

3138470

The results were as expected...

I can't point you to a reference on it but I have run into it in practice.

By combining Mr. CELKO's politely supplied DDL and sample data, with the technique described by Jeff Moden in his excellent SQL Spackle article: Group Islands of Contiguous Dates[/url], I can...

Personally, I think everyone in the US should get with the world and start formatting their phone numbers as:

+1 305 555 1212

Note that if you have Skype installed, that's a...

Neither of those last two CROSS APPLY examples will work in SS2K5 but this one will:

DECLARE @T AS TABLE

(

pk integer PRIMARY...

Another place it seems to be required is on the statement before certain DDL like ENABLE/DISABLE TRIGGER.

I would say it is a good practice to start adopting it now rather...

Here's another way that is a bit more terse:

DECLARE @T TABLE (ColName1 DATETIME, ColName2 DATETIME)

INSERT INTO @T

SELECT '2012-11-01', '2013-11-01'

UNION ALL SELECT '2012-11-01', '2013-11-02'

UPDATE @T

SET ColName1 = STUFF(ColName1, 8, 4, YEAR(ColName2))

WHERE...

Here's another example using PatternSplitCM (which can be found in the 4th link in my signature).

DECLARE @Phones TABLE

(PhoneID INT IDENTITY, PhoneNo VARCHAR(20))

INSERT INTO @Phones

SELECT '(305) 235-7513'

UNION...

murthyvs (12/14/2012)

---

dwain.c (12/11/2012)

---

Here's another way:

DECLARE @MyString VARCHAR(8000) =

'30;38469|31;38470|32;38471|33;38472|34;38473|35;38474|36;38475|37;38476|38;38477|'

SELECT col1=MAX(CASE ItemNumber%4 WHEN 1 THEN Item END)

,col2=MAX(CASE ItemNumber%4 WHEN 3 THEN Item END)

FROM...

Jeff has said (he can correct me if I'm wrong) that DelimitedSplit8K is optimized for VARCHAR(8000) so change it to VARCHAR(MAX) with that in mind.

I think although I don't have...

Cadavre (12/14/2012)

---

Don't suppose you still have the test...

lmeinke (12/14/2012)

---

Steve,

I was actually replying to dwain c. but I do thank you as well. I was out of the office for a few days and didn't try yours...

Steven - You're welcome and sorry again for sending you the initial red herring, although Jeff's article is also one to read and save.

Cadavre - I tested the Ben-Gan approach...

Sorry there my friend. I gave you the wrong link. Fortunately I saved a script with this incredibly elegant technique by SQL guru/MVP Itzik Ben-Gan.

DECLARE @T TABLE

...

Are you familiar with this article by Jeff Moden on grouping islands of contiguous dates?

http://www.sqlservercentral.com/articles/T-SQL/71550/

I believe you can probably use this technique to solve your problem. I confess, I...