SQL brian teaser...

Question

SQL brian teaser...

Viewing 15 posts - 46 through 60 (of 70 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Digs (7/4/2009)
Here is another tab split function that I pulled from a SQL 2000 book by Andrew Novick : Transact SQL UDFs
Which is better Lynns or Andrews ??? 😎

First, I agree with Jeff here. This thread now has plenty of information for testing these methods, including against some outrageously large strings.

Now, in just looking at the code you supplied, I have these observations (no, I'm not going to test it):

1. Since it was designed for SQL 2000, you are limited to an input string of 8000 characters. You can modify this to 2gb in sql 2005+ (change to max).

2. It does not return null items. I haven't tested this for any of the methods we tested, but I believe that all of these will return a null if one is in fact there.

3. It does not allow a space as a delimiter. All of our methods do.

4. Most important... this performs it's work in a while loop. aka pretty darn slow.

This method is very similar to the method I compared against in my article (see link in above post). So, without doing any actual testing, I would say that when you do test it, you will find it to be pretty slow. On the AdventureWorks test, ours were done in 1.5-6 seconds. I would expect to see 3+ minutes for this method (as I found in my article). But, test it yourself.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Digs SSCertifiable Points: 7333 More actions · Answer 2

Ok first thanks to the forum for the function 'DelimitedSplit'

This is my next challenge..

I need to do an insert into a table

FieldA, FieldB, FieldC

From my three data strings like:

'1,2,3'

'A,B,C'

'10,20,30'

I need to get the data like this, for the insert into my table

1 A 10

2 B 20

3 C 30

This code obviously was wrong

SELECT item from dbo.DelimitedSplit('1,2,3',',')

UNION ALL SELECT item from dbo.DelimitedSplit('A,B,C',',')

UNION ALL SELECT item from dbo.DelimitedSplit('10,20,30',',')

result

1

2

3

A

B

C

10

20

30

I need to do a join of some sort, but I have no key ?

Any ideas o - be - one ?:hehe:

UPDATE: My client side codes ensures that the items between the 3 strings is always equal and there are no NULLS.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Do you really want our help? If so, please review the code I provided in the SQLTeaserCode.txt file. Once you provide us with what we need in that format so that all we have to do is cut/paste/run in SSMS or QA, then we will help you.

Until then, you must learn to use the force!

We shouldn't have to write any code to setup the environment to work in. You need to help us help you.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Oh, by the way, I already have a solution ready once you post the setup code.

Digs SSCertifiable Points: 7333 More actions · Answer 5

Do you really want our help?

YES PLEASE

If so, please review the code I provided in the SQLTeaserCode.txt file.

Yes I have, that deals with one column possible insert, not mulitple.

Once you provide us with what we need in that format so that all we have to do is cut/paste/run in SSMS or QA, then we will help you.

I havent built it yet. Because what comes first the chicken or the egg, I am still working out the functionality of what can be done,before I lay down the hard work. You see I still dont know if my latest request can be done.

Can you use the the table

CREATE TABLE MYtable

(

FieldA INT,

FieldB VARCHAR(5),

FieldC INT,

)

INSERT INTO MYTable (FieldA, FieldB, FieldC)

SELECT etc etc your code

Please give me a break and post your fantastic code ??:-):-):-)

Pretty please !!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

And if you'd like a hint, it is all done using the dbo.DelimitedSplit function I provided. 😉

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

You are looking at the code in the wrong way. How did I provide you with everything you needed to test the code I provided?? Now, go back and look at it again. You should post everything I need to be able to test my solution.

Based on that, what do you need to provide me??

Remeber, what ever you provide, I should be able to cut/paste/run without any modification.

LutzM SSC Guru Points: 107049 More actions · Answer 8

Hi digs,

what's missing in your post is something like

CREATE TABLE #a(some cols)

INSERT INTO #a(selective cols)

SELECT [sample data] UNION ALL

SELECT [more sample data] UNION ALL

SELECT [even more sample data]

--target structure

CREATE TABLE #b(some target cols)

The phrase "Pretty please !!" usually has the effect of an immediate "freeze", meaning "nothing will happen after that".

If you'd like to warm up the "forum climate" again: just provide the sample data as asked by Lynn and as described in my signature (as well as in the sig of many other fellows around here)...

Note: like in a real world scenario you can't overheat the forum by posting the right amount of (useful) information.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Digs SSCertifiable Points: 7333 More actions · Answer 9

CREATE TABLE dbo.myTable

(

FieldA [INT],

FieldB [VARCHAR](5),

FieldC [INT],

)

And this failed...

INSERT INTO myTable (FieldA,FieldB,FieldC)

SELECT item from dbo.DelimitedSplit('1,2,3',',')

UNION ALL SELECT item from dbo.DelimitedSplit('A,B,C',',')

UNION ALL SELECT item from dbo.DelimitedSplit('10,20,30',',')

I am confused, I thought I did all that. Is the above ok ???

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

You have much to learn padawan.

Here:

declare @Str1 varchar(8000),

@Str2 varchar(8000),

@Str3 varchar(8000);

set @Str1 = '1,2,3';

set @Str2 = 'A,B,C';

set @Str3 = '10,20,30';

select * from dbo.DelimitedSplit(@Str1,',');

select * from dbo.DelimitedSplit(@Str2,',');

select * from dbo.DelimitedSplit(@Str3,',');

select

s1.Item,

s2.Item,

s3.Item

from

dbo.DelimitedSplit(@Str1,',') s1

inner join dbo.DelimitedSplit(@Str2,',') s2

on (s1.ItemID = s2.ItemID)

inner join dbo.DelimitedSplit(@Str3,',') s3

on (s1.ItemID = s3.ItemID);

Does this help you with your next step?

Digs SSCertifiable Points: 7333 More actions · Answer 11

Thanks the out put was

1A10

2B20

3C30

Where did the column 'ItemID' come from, have you made changes to the 'DelimitedSplit' function, by allowing and 'Identitiy field ' (increment at 1) .

If you have made changes to the function ( I assume you have) could you please post it here thanks.

YES I can do an INSERT from your demo, thanks:-)

UPDATE:

Now I know why you ribbed me so much , i cracked it..

select

row_number() over (order by ItemOrder) as ItemID,

Item

from

ItemSplit

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

Digs (7/5/2009)
Ok first thanks to the forum for the function 'DelimitedSplit'
This is my next challenge..
I need to do an insert into a table
FieldA, FieldB, FieldC
From my three data strings like:
'1,2,3'
'A,B,C'
'10,20,30'
I need to get the data like this, for the insert into my table
1 A 10
2 B 20
3 C 30

Just curious... what are the business requirent behind doing this? What's this for?

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

LutzM SSC Guru Points: 107049 More actions · Answer 13

Hi Digs,

I'm just curious:

Have you ever tried the DelimitedSplit function as per this post?

If yes, then you'd have noticed there always have been two columns...

When looking at the code of the function you'll find the following three lines at the end of it:

select

row_number() over (order by ItemOrder) as ItemID,

Item

Those are the line actually selecting the return values.

One of them is [ItemID].

So, as far as i can see, there's nothing new...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Digs SSCertifiable Points: 7333 More actions · Answer 14

Thanks all sorted now !

This code is for an ASP.net project, data input comes from 3 textboxes that will out put a string each.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 15

Digs (7/5/2009)
Thanks the out put was
1A10
2B20
3C30
Where did the column 'ItemID' come from, have you made changes to the 'DelimitedSplit' function, by allowing and 'Identitiy field ' (increment at 1) .
If you have made changes to the function ( I assume you have) could you please post it here thanks.
YES I can do an INSERT from your demo, thanks:-)
UPDATE:
Now I know why you ribbed me so much , i cracked it..
select
row_number() over (order by ItemOrder) as ItemID,
Item
from
ItemSplit

Thank you for the feedback, and I'm glad you figured out the code I provided.

Now, the only thing left, young padawan, is to learn how to post questions to get the best answers. Please read the first article I reference below in my signature block, and review how I have posted code for you in this thread (wether as an attachment, or directly). The more you do up front for us, the better answers you will get in return.