Virtual PM's for Jeff...

Question

Virtual PM's for Jeff...

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004582 More actions · Answer 1

Matt Miller (6/18/2008)
Heck I often think I get more out of doing the actual testing than those watching on the sides, so it's certainly not entirely altruistic

Thanks for looking for those tests... yeah, I think they all slid into the 500+ bit bucket.

So far as testing, writing articles, and solving posts... I'm right there with ya... no better teacher than doing...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Here you go:

ALTER function [dbo].[fnSplit1](

@parameter varchar(Max)-- the string to split

, @Seperator Varchar(64)-- the string to use as a seperator

)

RETURNS @Items TABLE(

ID INT-- the element number

, item VARCHAR(8000)-- the split-out string element

, OffSet int-- the original offest

--( not entirley accurate if LEN(@Seperator) > 1 because of the Replace() )

)

AS

BEGIN

/*

"Monster" Split in SQL Server 2005

From Jeff Moden, 2008/05/22

BYoung, 2008/06/18: Modified to be a Table-Valued Function

And to handle CL/LF or LF-only line breaks

(Note: make it inline later, to make it faster)

Test: (scripts all triggers in your database)

Select Lines.Item

From sys.sql_modules M

Join sys.objects O on O.object_id = M.object_id

cross apply dbo.fnSplit1(M.definition, char(13)+char(10)) Lines

Where O.Type = 'TR'

Order by O.create_date, Lines.ID

*/

Declare @Sep char(1)

Set @Sep = char(10)--our seperator character

--NOTE: we make the @Sep character LF so that we will automatically

-- parse out rogue LF-only line breaks.

--===== Add start and end seprators to the Parameter so we can handle

-- all the elements the same way

-- Also change the seperator expressions to our seperator

-- character to keep all offsets = 1

SET @Parameter = @Sep+ Replace(@Parameter,@Seperator,@Sep) +@Sep

;WITH cteTally AS

(--==== Create a Tally CTE from 1 to whatever the length

-- of the parameter is

SELECT TOP (LEN(@Parameter))

ROW_NUMBER() OVER (ORDER BY t1.Object_ID) AS N

FROM Master.sys.All_Columns t1

CROSS JOIN Master.sys.All_Columns t2

)

INSERT into @Items

SELECT ROW_NUMBER() OVER (ORDER BY N) AS Number,

SUBSTRING(@Parameter, N+1, CHARINDEX(@Sep, @Parameter, N+1)-N-1) AS Value

, N+1

FROM cteTally

WHERE N < LEN(@Parameter)

AND SUBSTRING(@Parameter, N, 1) = @Sep --Notice how we find the seperator

Return

END

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004582 More actions · Answer 3

Cool! Thanks, Barry.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

info@kraaitje.nl SSC-Addicted Points: 406 More actions · Answer 4

Thanks Barry, great function. I use it when generating procedures using dynamic SQL to view the output. Normally, I use PRINT @sql but the @sql variable is more then the number of characters SSMS can display. Now, I use:

SELECT * FROM dbo.fnSplit1(@SQL, char(13)+char(10)) Lines

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

kraaitje (2/3/2010)
Thanks Barry, great function. I use it when generating procedures using dynamic SQL to view the output. Normally, I use PRINT @sql but the @sql variable is more then the number of characters SSMS can display. Now, I use:
SELECT * FROM dbo.fnSplit1(@SQL, char(13)+char(10)) Lines

Cool! Glad I could help. Jeff had suggested to me some time ago that I should write this all up (the tricks, traps and gotchas of large-scale auto-scripting, and my solutions) as an article, might still be a good idea for me.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004582 More actions · Answer 6

I still think it would be a great article. Lots of people run into the same problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Virtual PM's for Jeff...

Cookies on SQLServerCentral