Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Joy of Numbers


The Joy of Numbers

Author
Message
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168
Comments posted to this topic are about the content posted at temp
David le Quesne
David le Quesne
SSC Eights!
SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)

Group: General Forum Members
Points: 819 Visits: 32

Great article Mike, thanks, and what a neat little function SetSplit is! I have added it to my toolkit (and given you and SQLServerCentral the credit), but added a second parameter for the character to split the string with.

I had to sit down and figure out how it worked however...

I was wondering how you could number the result set sequentially, so that with you could select from the function to return the nth word in the result set. I don't think you can use an of the methods I would normally use, IDENTITY(), or count(*) and a GROUP BY

David



If it ain't broke, don't fix it...
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168

Thanks David. If you're on SQL 2005 it's a simple matter of converting the function to a CTE and adding a ROW_NUMBER() to it. I set it up that way initially but noticed some performance was lost. For SQL 2000 it's more complicated, and your best bet might be to change the UDF to a regular table-valued function and INSERT the results into a table variable with an IDENTITY column. Again, you'll lose performance that way, but depending on your situation it might be worth it.

Below is an example on SQL 2005 using a CTE and the ROW_NUMBER() function. Note that I don't have a SQL 2005 installation handy, so I wasn't able to test this before posting. There may be a syntax error or two in it, but you get the idea.

CREATE FUNCTION dbo.fnSetSplit (@String VARCHAR(8000))
RETURNS TABLE
AS
RETURN (
WITH Splitter(Num, String) AS
(
SELECT Num,
SUBSTRING(@String,
CASE Num
WHEN 1 THEN 1
ELSE Num + 1
END,
CASE CHARINDEX(',', @String, Num + 1)
WHEN 0 THEN LEN(@String) - Num + 1
ELSE CHARINDEX(',', @String, Num + 1) - Num -
CASE
WHEN Num > 1 THEN 1
ELSE 0
END
END
) AS String
FROM dbo.Numbers
WHERE Num <= LEN(@String)
AND (SUBSTRING(@String, Num, 1) = ','
OR Num = 1)
)
SELECT ROW_NUMBER() OVER (ORDER BY Num) AS Num, String
FROM Splitter
)


YSLGuru
YSLGuru
SSC Eights!
SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)

Group: General Forum Members
Points: 959 Visits: 1659

In addition to the benefits of a NUMBERS table shown by Micahel, the ability to intentionally generate a Cartisian product is anoter use of the table. I'm referring to a Cartesion product where you want X number of rows not to perform some function but to get X number of rows of data that would otherwise require X number of UNION statements. In our company we provide custom reporting for clients. On a few occassions I have seen some of our 'SQL Knowledge Limited' report writers create code that contains several queries UNION'd together because they need to generate a specific number of records from a query that would normally generate just 1 record. I don't recall the specifics, only that I improved the query's performance greatly by using a NUMBERS table approach to generate the X number of copies of a record that the prior UNION version was doing.

On a similiar note a DATE table is another wonderful utility table that works similiar to a NUMBERS table. I have a utility table I call DATELOOKUP that conatins 1 row for every date (MM/DD/YYYY) between a range of years that covers any time frame that a client would need to report on for now and for the next 10 years. Each row has many columns, each column containing a piece of info specific to the date. For example in our business it's important to use the first day of a month specified by the user. Without the date table, a combination of Date functions (like DateAdd) have to be used to get the first day of whatever MM/DD/YYYY a user enters as criteria. WIth the DATELOOK table I can join the data in DATELOOKUP to the date the user specifies and return the column from that record that contains the first day of said Month/Year. IN fact the code for generating and using the DATELOOKUP table is on the SQLServerCentral site within sample scripts.

Excellent article and information!

Thanks for sharing!

Ed



Kindest Regards,

Just say No to Facebook!
nigelrivett
nigelrivett
Old Hand
Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)Old Hand (396 reputation)

Group: General Forum Members
Points: 396 Visits: 211

You might want to have a look at

http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-common-table-expressions/

Which shows how this sort of thing can be done in v2005.

Must admit I never create a permanent table like this but always create it on the fly - either as a derived table or table variable.




Cursors never.
DTS - only when needed and never to control.

corey lawson
corey lawson
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 544
OK, I'll invoke the Calendar table that another poster mentioned. In other circles, this is attributed sometimes to Joe Celko, and a "Celko is an Idiot!" flamefest ensues... But in some ways, if you just have a numbers table, you can use date math to do it as well...

but it really is probably the slickest ways to deal with date ranges (i.e., produce a result set that has the start date, end date, and all the dates between it) in SQL. Of course, it's easy enough to do it procedurally, but that kind of defeats the purpose of SQL in my book at least.
Mike C
Mike C
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1351 Visits: 1168

Celko definitely knows what he's doing and he writes good books. I think people just get rubbed the wrong way by some of his responses on the newsgroups... but that's neither here nor there...

Aaron Bertrand has some excellent articles on using an Auxiliary Calendar Table over at ASPFAQ: http://www.aspfaq.com/show.asp?id=2519. I highly recommend using a date-based calendar table similar to the one he describes, because of the flexibility. You can use a calendar table like his to mark off regional holidays, easily calculate working days in a time period, etc. While you could probably do the same type of thing with a numbers table, it won't be as easy or as intuitive.

For Nigel: I prefer to use a single permanent numbers table. You eliminate the cost of re-creating it over and over (can become pretty substantial if you're re-creating it a lot and inserting a lot of numbers into it) and you can get a potential performance boost by using WITH SCHEMABINDING with UDF's that reference your permanent numbers table.

Thanks


B Bulhan
B Bulhan
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 1
Thanks for the article. It certainly is useful.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45137 Visits: 39923
Nicely done, Michael. Real good to see someone actually do a comparison in performance, too.

Since you were kind enough to mention my name, I have to sing the praises of the guy that got me into using Tally/Numbers tables... Adam Machanic.

Since that time, I've run into several DBA's that absolutely refused to allow a permanent Tally table (never mind a date table) to exist but would, strangly enough, allow a temp table... I needed a very high speed method to make one and, well, the rest is history.

Great article and good references... wouldn't mind seeing you do a follow-up article on more Tally table techniques.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Fal
Fal
Mr or Mrs. 500
Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)Mr or Mrs. 500 (522 reputation)

Group: General Forum Members
Points: 522 Visits: 1803

What a useful trick, and definitely something I'll need to remember.

However, I think the function has a bug. When I put through either of the following...

SELECT * FROM dbo.fnSetSplit(',C,,,E')

SELECT * FROM dbo.fnSetSplit(',,C,,,E')

...I get odd results before 'C'. Instead, I knocked up the below with some adjustments:

i) the Numbers table starts from 0, not 1
ii)
accepts a variable delimiter, including one of variable length;
iii) returns a row number so that you can choose the nth value.
iv) use "like" for comparisons as SQL Server can do funny things comparing spaces

It does have a "quirk", however. If the delimiter is a multiple of the same character you can get odd results. Whether this is an error or not I think depends on the expected usage.

SELECT * FROM dbo.fnSetSplitSV(',,C,,,E',',') -- picks up the "missing" A

SELECT * FROM dbo.fnSetSplitSV(' TA fred TA mary TA albert',' TA ') -- variable delimiter

SELECT * FROM dbo.fnSetSplitSV(',,,C,,,E',',,') -- multiple delimiter with "quirk"

S.

(Sorry if the below comes over with screwy formatting.)

CREATE FUNCTION [dbo].[fnSetSplitSV] (@String VARCHAR(8000), @Delim VARCHAR(5))

RETURNS @SplitTable TABLE

(

Row int identity

, Num int

, String varchar(8000)

)

AS

BEGIN

DECLARE @DelimLen int

SELECT @DelimLen = datalength(@Delim)

INSERT INTO @SplitTable

SELECT

Num

, SUBSTRING(@String

, CASE Num + @DelimLen

WHEN @DelimLen THEN @DelimLen

ELSE Num + @DelimLen

END - @DelimLen

, CASE CHARINDEX(@Delim, @String, Num)

WHEN 0 THEN LEN(@String) - Num + @DelimLen

ELSE CHARINDEX(@Delim, @String, Num) - Num

END

) AS String

FROM dbo.Numbers

WHERE Num <= LEN(@String)

AND (SUBSTRING(@String, Num - @DelimLen, @DelimLen) like @Delim OR Num = 0)

RETURN

END


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search