Convert String to a Table using CTE

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 Amit Gaur » Convert String to a Table using CTE

74 posts, Page 8 of 8

«««4 5 6 7 8

Convert String to a Table using CTE

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Saturday, December 26, 2009 1:09 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 32,910, Visits: 26,800

Yeah... but confirmation of those things is always a good thing. Tongue

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #839195

zukko

Posted Wednesday, February 24, 2010 1:48 PM

Valued Member

Group: General Forum Members
Last Login: Friday, February 15, 2013 7:51 AM
Points: 53, Visits: 85

using this with more than 100 rows with the recursive option set to 0, makes it slower that using the while version of it Sad

Post #872267

phickey

Posted Friday, May 13, 2011 4:12 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 7:45 AM
Points: 4, Visits: 77

msaleem-583379 has a pretty good function. I modified it to return varchar instead of int values. As such, it needed a couple of improvements:

1. Empty strings or spaces on either side of a comma returned a row, but emtpy string or spaces with no comma returned no rows, which is inconsistent. I made it return one row with an emtpy string when receiving an empty string for input. (NULL still returns 0 rows.)

2. If last item in input was spaces, it returned empty string; in other positions, the spaces were returned. I made it return all spaces in last item.

These changes required:

a. Removing "Or Len(@csv) = 0" from the first IF.

b. Getting the full length of the input with "@RealLength = len(@csv + 'x') - 1" and using @RealLength instead of len(@csv) in the final SELECT.

Post #1108751

Jeff Moden

Posted Friday, May 13, 2011 4:36 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:51 PM
Points: 32,910, Visits: 26,800

phickey (5/13/2011)

msaleem-583379 has a pretty good function.

Where is that? What is the URL? I don't see such an entry on this thread.

And, for the record, the "performance issue" with the Tally Table has very successfully been put to bed for VARCHAR(8000) and NVARCHAR(4000). Please see the following article...
http://www.sqlservercentral.com/articles/Tally+Table/72993/

And, yes, I realize I'm responding to a post that is responding to a two year old thread. Wink

--Jeff Moden

Post #1108753

« Prev Topic | Next Topic »

74 posts, Page 8 of 8

«««4 5 6 7 8

Permissions