Function to split string

Question

Function to split string

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: 1003865 More actions · Answer 1

Thanks for the cover, Thom. I should have posted the links like you did.

--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)

Goffr Newbie Points: 5 More actions · Answer 2

Thank you! First I've heard of a Tally table. I'm confused on one point in your discussion. You say,

The largest number in the Tally table should not be just some arbitrary choice. It should be based on what you think you'll use it for. I split VARCHAR(8000)'s with mine, so it has to be at least 8000 numbers. Since I occasionally need to generate 30 years of dates, I keep most of my production Tally tables at 11,000 or more which is more than 365.25 days times 30 years.

As I understand the second and third sentences, the Tally Table should contain one row for each character in the parameter. My reading and testing of your code supports this conclusion. On the other hand, the fourth sentence seems to suggest that the Tally Table should contain one row for each value in the parameter. How should I understand this?

Apologies to everybody who already knows the answer. You were once where I am.

Rick

jcelko212 32090 SSCrazy Eights Points: 9294 More actions · Answer 3

>> I have this function, but I wanted to pass a table so as to use the same function to get the job done for multiple tables. For example, I want this function to work for table1, and table2. But it is just for table1 currently. I was trying to use a dynamic sql in vain; it doesn't pass the parameter selected. Can someone help? Give me a guide on how to pass a table as a parameter. <<

Your approach to SQL is wrong. A table is supposed to model a single specific set of entities, not some vague generic thing like a file system did. The phrase for this is "an automobile, squids, and Lady Gaga table" and it makes no sense, thanks to a principle of logic called the Law of Identity.

You can obviously write functions that work on a particular data type, and invoke them on the columns of a table.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 4

Goffr wrote:

Thank you! First I've heard of a Tally table. I'm confused on one point in your discussion. You say,
The largest number in the Tally table should not be just some arbitrary choice. It should be based on what you think you'll use it for. I split VARCHAR(8000)'s with mine, so it has to be at least 8000 numbers. Since I occasionally need to generate 30 years of dates, I keep most of my production Tally tables at 11,000 or more which is more than 365.25 days times 30 years and a whole lot of other stuff that generally requires much lower rowcounts. The key is to limit what you want with a WHERE clause.
As I understand the second and third sentences, the Tally Table should contain one row for each character in the parameter. My reading and testing of your code supports this conclusion. On the other hand, the fourth sentence seems to suggest that the Tally Table should contain one row for each value in the parameter. How should I understand this?
Apologies to everybody who already knows the answer. You were once where I am.
Rick

Totally agreed on knowing where you're at.

The Tally table and its "readless cousin", the fnTally Function, aren't just used for splitting parameters. They're also used to replace a wide variety of things that would ordinarily required some form of "RBAR" (While Loop, Recursive CTEs, Scalar and Multi-statement Inline Table Valued functions {mTVF for short}, and, of course, Cursors). The statement you cite about why I made mine 11,000 rows is because I use it to rapidly create a list of 30 years of dates (365.25 * 30 = ~10,957 days).

Even though a physical Tally table is a bit faster than the fnTally Function (which uses "Cascading CTEs" or cCTEs for short and is different than "Recursive CTEs" or rCTEs for short), I don't use the physical table as much as I used to simply because of the logical reads it generates like any table would. The fnTally Function, by itself, produces zero reads.

Both can also be used as a "row source" to quickly generate millions of rows of random but constrained test data and will blow the doors off almost every other method of generating such table.

Here are a couple of more links to have a gander at in the area of generating test data. Just replace the Cross Join of sys.all_columns (a "row source" also known as a "Pseudo Cursor" with a single call to fnTally with the correct parameters.

https://www.sqlservercentral.com/articles/generating-test-data-part-1-generating-random-integers-and-floats-1

https://www.sqlservercentral.com/articles/generating-test-data-part-2-generating-sequential-and-random-dates

The last link in my signature line below will take you to one version of the fnTally function.

--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)

Goffr Newbie Points: 5 More actions · Answer 5

I don't have anything to add. I just wanted to say thanks for your informative responses and articles. I have shared some of your links with a coworker who is way more experienced than I am but hasn't considered tally tables.

Rick

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

Thanks for the feedback, Rick.

--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)