Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««45678

Convert String to a Table using CTE Expand / Collapse
Author
Message
Posted Saturday, December 26, 2009 1:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
Yeah... but confirmation of those things is always a good thing.

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #839195
Posted Wednesday, February 24, 2010 1:48 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued 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
Post #872267
Posted Friday, May 13, 2011 4:12 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 8:38 AM
Points: 7, Visits: 98
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
Posted Friday, May 13, 2011 4:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:19 PM
Points: 36,724, Visits: 31,173
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.


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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1108753
« Prev Topic | Next Topic »

Add to briefcase «««45678

Permissions Expand / Collapse