SQL Clone
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
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6459 Visits: 1172
Comments posted to this topic are about the content posted at temp
David le Quesne
David le Quesne
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1617 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
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6459 Visits: 1172

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
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4412 Visits: 1667

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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1084 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 Eights!
SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)

Group: General Forum Members
Points: 923 Visits: 575
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
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6459 Visits: 1172

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
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

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

Group: General Forum Members
Points: 213737 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Fal
Fal
Right there with Babe
Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)Right there with Babe (778 reputation)

Group: General Forum Members
Points: 778 Visits: 1809

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