Another way to get a table list from a string

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Bernabe Diaz » Another way to get a table list from a string...

Another way to get a table list from a string

Rate Topic

Display Mode

Topic Options

Author

Message

diaz.bernabe

Posted Tuesday, June 15, 2010 12:09 AM

Old Hand

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:10 AM
Points: 313, Visits: 168

Comments posted to this topic are about the item Another way to get a table list from a string

Post #937261

deanroush

Posted Tuesday, June 15, 2010 7:52 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 02, 2013 9:10 PM
Points: 26, Visits: 263

Excellent! I have two other procs that do this but not with such elegance. i will be testing performance of the three versions soon and will add findings. The other versions also return only strings.
I did have to add one more single quote at the end to make it work.

Post #937513

mister.magoo

Posted Tuesday, June 15, 2010 12:35 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 5:50 PM
Points: 1,308, Visits: 3,899

Thanks for the Article.

For further reading, anyone interested in using xml to split strings may be interested in this excellent blog post from Brad Schulz

http://bradsruminations.blogspot.com/2010/01/delimited-string-tennis-again-final.html

in which he describes a few tweaks that really speed things up...

MM

Post #937758

sjsubscribe

Posted Wednesday, June 16, 2010 11:36 AM

SSC Veteran

Group: General Forum Members
Last Login: Thursday, December 20, 2012 1:03 PM
Points: 265, Visits: 589

Good parsing technique.

Even though @strDataSet is declared as max, aren't the procedure parameters limited to max length of only 128 characters? That limits the longest string that can be parsed to that limit. Right?

Post #938445

aaron.bertrand

Posted Thursday, June 17, 2010 11:13 AM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 7:46 AM
Points: 31, Visits: 503

This is clever and a little more flexible than some of the other techniques - and useful if all you need to do is return the output directly to the caller. But the weakness is that the dynamic SQL means it can't be moved to a function - using a procedure really limits what you can do with the output. I've had very good results that use a table-valued function and a numbers table to parse lists, and then the TVF results can be used in JOINs and other structs, unlike the output of a stored procedure (which can only be dumped to a temp table if further processing or filtering is required).

Post #939111

gabriele.acconcia

Posted Monday, August 09, 2010 9:14 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, September 06, 2012 4:11 AM
Points: 2, Visits: 8

Is the script correct? When I run it, I got an "Unclosed quotation mark after the character string '" error message. So I commented out the last + ' (that before the EXEC(@STR) statement) and could create the stored procedure.
After the fix, the stored procedure seems to work fine.
Best regards

Post #966036

« Prev Topic | Next Topic »

Permissions