Parse query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Parse query

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-03 : 15:31:28

I need a query to split the text type comments data into multiple rows when the length of text value is greater than 1500.

Thanks for your help in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-04 : 10:37:27

do you mean datatype is text or is it varchar?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-04 : 12:36:03

source is text data type and destination is varchar type

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-03-04 : 12:49:00

Did you use the split function ?

If you want to split the comments by certain rules, what are your rules?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-04 : 13:08:12

Most split functions that you will find on this forum and elsewhere split based on a delimiter such as comma. If you want to split to fixed length slices, you will need to make slight changes to those (to look for a specific length instead of the delimiter). Here is a quick and dirty way of doing it - probably not the most efficient:

CREATE TABLE #tmp (id INT, x NTEXT);
INSERT INTO #tmp VALUES (1,REPLICATE('a',45)),(2,REPLICATE('b',22));

GO

DECLARE  @sliceLength INT =  10;
;WITH cte AS
(
	SELECT 
		1 lvl,
		id, 
		LEFT(CAST(x AS VARCHAR(MAX)),@sliceLength) as s, 
		STUFF(CAST(x AS VARCHAR(MAX)),1,@sliceLength,'') AS r 
	FROM #tmp
	
	UNION ALL
	
	SELECT 
		lvl+1,
		id, 
		LEFT(r,@sliceLength), 
		STUFF(r,1,@sliceLength,'') 
	FROM cte
	WHERE r <> '' 
)
SELECT id,s FROM cte ORDER BY id,lvl
OPTION (MAXRECURSION 0);

GO

DROP TABLE #tmp;

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-03-05 : 12:07:22

Thanks sunitabeck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources