Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

The Joy of Numbers Expand / Collapse
Author
Message
Posted Sunday, August 13, 2006 8:22 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132
Comments posted to this topic are about the content posted at temp
Post #301432
Posted Monday, August 14, 2006 4:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 4:54 AM
Points: 815, 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...
Post #301470
Posted Monday, August 14, 2006 7:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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
)

Post #301525
Posted Monday, August 14, 2006 8:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, March 10, 2014 1:21 PM
Points: 885, Visits: 1,526

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,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.
Post #301595
Posted Monday, August 14, 2006 8:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 13, 2013 4:35 AM
Points: 386, 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.
Post #301614
Posted Monday, August 14, 2006 1:52 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:09 PM
Points: 154, Visits: 323
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.
Post #301690
Posted Monday, August 14, 2006 2:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 14, 2014 4:18 PM
Points: 1,276, Visits: 1,132

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

Post #301711
Posted Monday, August 14, 2006 7:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, August 19, 2007 7:13 PM
Points: 1, Visits: 1
Thanks for the article. It certainly is useful.
Post #301762
Posted Monday, August 14, 2006 8:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 10:03 PM
Points: 35,956, Visits: 30,244
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #301768
Posted Monday, August 14, 2006 9:00 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, January 19, 2014 5:00 PM
Points: 605, Visits: 1,690

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

Post #301769
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse