Another way to get a table list from a string

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

  • 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.

  • 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

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


    select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

  • Forum Etiquette: How to post Reporting Services problems
  • [/url]
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • [/url]
  • How to Post Performance Problems - by Gail Shaw
  • [/url]

  • 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?

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

  • 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

  • Thanks for the script.

  • Viewing 7 posts - 1 through 6 (of 6 total)

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