Carriage Return Line return - 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 2005 Forums

Transact-SQL (2005)

Carriage Return Line return

Author

Topic

vqcheese
Starting Member

19 Posts

Posted - 2009-06-19 : 09:21:23

I have a simple sql table, this table has 1 field called notes32000_i, this field is a textfield, or a notes field and has over 1000 characters at least. How could i have it return a new line after every 100 characters.
Here is my SELECT statement:

select notesindex_i, notes32000_i
from hr2not01

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-19 : 10:06:10

If it is possible then you should do that in your front end application.

Greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

vqcheese
Starting Member

19 Posts

Posted - 2009-06-19 : 10:31:02

I thought about that too, im pulling in a view using excel 2007, then using the pivot table to show my data, im nto sure how to do this in a pivot table...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-19 : 10:59:54

As webfred sais -- this is a lot better to do in the front end.

Here's one way to do it in the back end though! -- This is a dirty hack and will be limited to 32767 recursions which is to strings of less than 3276700 characters.

NB:This is a a really bad thing to do in the db and there are probably much better ways to do it if you did have to but I wans interested in shoe horning a recursive CTE to do the job!


DECLARE @foo TABLE (
	[ID] INT
	, [value] NVARCHAR(MAX)
	)

INSERT @foo ([Id], [value])
      SELECT 1, 'aslkdjkjfa;sdlgkjal;djkg;aldgjka;erg agna;odfbj;arji;hjaer;gadfg;adfngadfnghladfjgha;df jv;av;aerjg;aeriojg;aj;dknvadfnkgbdfjgjae;origj;adjg;adflkg;adfnkb;adfnbafd;ogj;aeroijg;aerg;dfjkbnladfkjbnafdhgo;ierg;oamgl;dfknv;akdfbnladfigh;erjg;aejg;kadfjbgkl;dflnb;adfbasdfasdfasdfawefasdgasdhadfhafdhddh'
UNION SELECT 2, 'This is a lot of text that hopefully adds up to more that 100 characters. This is a lot of text that hopefully adds up to more that 100 characters. This is a lot of text that hopefully adds up to more that 100 characters. This is a lot of text that hopefully adds up to more that 100 characters. This is a lot of text that hopefully adds up to more that 100 characters. This is a lot of text that hopefully adds up to more that 100 characters.'
UNION SELECT 3, 'Not very much text here'

SELECT
	[ID]
	, LEN([value])
FROM
	@foo

;WITH splitter AS (
SELECT
	f.[Id] AS [fooID]
	, SUBSTRING(f.[value], 1, 100) AS [Text]
	, 100 AS [pointer]
	, 1 AS [lineNo]
	, 0 AS [backPointer]
FROM
	@foo f
UNION ALL SELECT
	f.[ID]
	, SUBSTRING(f.[value], s.[pointer] + 1, 100)
	, s.[pointer] + 100
	, s.[lineNo] + 1
	, s.[backPointer] + 1
FROM
	@foo f
	JOIN splitter s ON
		s.[fooID] = f.[ID]
		AND s.[lineNO] - 1 = s.[backPointer]
		AND s.[pointer] < LEN(f.[value])
)
SELECT [fooID], [lineNo], [text], LEN([text]) FROM splitter
ORDER BY
	[fooId]
	, [lineNo]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources