The Joy of Numbers
-
Comments posted to this topic are about the content posted at temp
-
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...
-
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
)
-
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! -
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. -
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.
-
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
-
Thanks for the article. It certainly is useful.
-
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.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) -
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
-
Thank you, Mike - this is one of the most useful and best-written articles I've ever seen on a practical SQL Server topic.
I admit to being guilty of using a procedure method of doing the digest-the-delimited-list thing. As of tomorrow moring, I'm switching to your technique.
I really like the idea of using auxilliary tables like this for set-based, rather than procedural data manipulation. I, too, have to credit Joe Celko with that idea - I read about calendar tables in one of his articles.
In my current project, which involves writting stored procedures to feed Crystal Reports, I found that I could use a calendar table coupled with a cross-join to do in a fraction of a second what had been done earlier with time-consuming, loop-di-loop procedural code.
I recommend that anyone who hasn't tried this powerful duo of auxilliary tables (numbers, calendar, etc) and cross-joins try it out
Regards,
SteveR
-
Hi Simon,
Thanks for the catch. I don't know why I didn't test it with data with an empty first element in the list! (Doh!) Here's your solution converted over to take advantage of the SQL 2005 ROW_NUMBER() function and CTEs:
CREATE
FUNCTION dbo.fnSetSplitSV (@String VARCHAR(MAX),
@Delim VARCHAR(5))
RETURNS TABLE
AS
RETURN
(
WITH Splitter (Num, String)
AS
(
SELECT Num, SUBSTRING(@String,
CASE Num + DATALENGTH(@Delim)
WHEN DATALENGTH(@Delim) THEN DATALENGTH(@Delim)
ELSE Num + DATALENGTH(@Delim)
END - DATALENGTH(@Delim),
CASE CHARINDEX(@Delim, @String, Num)
WHEN 0 THEN LEN(@String) - Num + DATALENGTH(@Delim)
ELSE CHARINDEX(@Delim, @String, Num) - Num
END
) AS String
FROM dbo.Numbers
WHERE Num <= LEN(@String)
AND (SUBSTRING(@String, Num - DATALENGTH(@Delim),
DATALENGTH(@Delim)) LIKE @Delim
OR Num = 0)
)
SELECT ROW_NUMBER() OVER (ORDER BY Num) AS Row, Num, String
FROM Splitter
)
-
Thanks everyone, this has been really useful!
I have used the function to return all strings in the set after the nth string using a little feature for concatenating strings I found on this site...
eg:
DECLARE @strReturn VARCHAR(8000)
SELECT @strReturn = COALESCE(@strReturn,'')+String+' '
FROM dbo.fnSetSplitSV('The Quick Brown Fox Jumped Over the Slow Lazy Dog',' ')
WHERE Row >= 8
SELECT @strReturn
this will return 'Slow Lazy Dog'
Cheers
David
If it ain't broke, don't fix it...
-
Neat - I've not seen a select that way before. Closest I've come is something along the lines of "SELECT dbo.fn(...) FROM <table>"
Only change I'd make is initialize @strReturn first, lest you call COALESCE for every single row from the table when it only applies to the first row. (Although if NULL comes back then @strReturn will still be null - depends if that is a problem or not.) Something like:
DECLARE
@strReturn VARCHAR(8000)
SET
@strReturn = ''
SELECT
@strReturn = @strReturn + String + ' '
FROM dbo.fnSetSplitSV('The Quick Brown Fox Jumped Over the Slow Lazy Dog',' ')
WHERE Row >= 8
SELECT
@strReturn
S.
-
Be careful with that:
SELECT @strReturn = COALESCE(@strReturn,'')+String+' '
FROM dbo.fnSetSplitSV('The Quick Brown Fox Jumped Over the Slow Lazy Dog',' ')
WHERE Row >= 8
AFAIK, you can't rely on the results being returned in any particular order when doing a concantenation like that from a SELECT statement. You could conceivably end up with 'Lazy Dog Slow' or 'Dog Lazy Slow' or some other out-of-order result if SQL Server decided it was more efficient to deliver the results of the SELECT in a different order.
Viewing 15 posts - 1 through 15 (of 42 total)
You must be logged in to reply to this topic. Login to reply