June 4, 2009 at 1:49 am
I HAVE A CHARACTER LIKE THIS FOR EG ('THIS IS A TEXT MESSATE SORRY IT IS A MESSAGE'). I HAVE TO INSERT A LINEFEED/CAR-RETURN BETWEEN 15 CHAR IF THE CHAR BREAKS THEN I HAVE TO FIND SPACE BEFORE THAT CHAR AND GIVE A LINE FEED FOR EG(THE 15 TH CHAR IS S THEN IT SHOULD GIVE BEFORE M .
THIS IS A TEXT MESSATE SORRY IT IS A MESSAGE
SHOULD RESULT AS
---------------
THIS IS A TEXT
MESSATE SORRY
IT IS A MESSAGE
June 4, 2009 at 3:50 am
print 'hello' + char(13) + char(10) + 'world'
you can do something like that i suppose.
June 4, 2009 at 7:25 am
PRINT 'Hello
World'
June 4, 2009 at 3:56 pm
From your post I assume you are looking for a bit of code that can take a string and format in the way you have stated.
This might help:
DECLARE@vcStrvarchar(100),
@vcResultvarchar(200),
@iStartint,
@iLenint,
@iSpaceAtint
SET @vcStr = 'THIS IS A TEXT MESSATE SORRY IT IS A MESSAGE'
SET @vcResult = ''
SET @iLen = LEN(@vcStr)
SET @iStart = 1
WHILE @iStart 0
AND @iLen - @iStart > 15
BEGIN
/* Use REVERSE to find last occurrence of a space in the current 15 characters */
SET @iSpaceAt = 16 - CHARINDEX(' ',REVERSE(SUBSTRING(@vcStr,@iStart,15)))
/* build up result string */
SET @vcResult = @vcResult + SUBSTRING(@vcStr,@iStart,@iSpaceAt - 1) + CHAR(13) + CHAR(10)
SET @iStart = @iStart + @iSpaceAt
END
ELSE
BEGIN
/* append remaining chunk of data*/
SET @vcResult = @vcResult + SUBSTRING(@vcStr,@iStart,(@iLen - @iStart) + 1)
SET @iStart = @iLen + 1
END
END
SELECT@vcResult
Mike
June 5, 2009 at 1:43 am
Try something like this...
DECLARE @data varchar(50)
DECLARE @Value int
DECLARE @Count int
DECLARE @val varchar(16)
SET @data = 'THIS IS A TEXT MESSATE SORRY IT IS A MESSAGE'
SET @Value = 1
SET @Count = 1
WHILE @Count <= LEN(@Data)
BEGIN
SELECT @val = SUBSTRING(@Data,@Value - 1,15) + CHAR(10) + CHAR(13)
SELECT @val
SET @Value = @Value + 15
SET @Count = @Count + 15
END
October 2, 2015 at 12:28 pm
is there a way of doing this with multiple rows that vary in length? i have 6000+ rows that have variable text and need a hard break after each period.
any suggestion would help
October 2, 2015 at 12:36 pm
dan.maddux (10/2/2015)
is there a way of doing this with multiple rows that vary in length? i have 6000+ rows that have variable text and need a hard break after each period.any suggestion would help
You could try something like:
REPLACE(YourText, '.', '.' + CHAR(13) + CHAR(10))
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
October 2, 2015 at 12:36 pm
REPLACE(fields_name, '. ', '. ' + CHAR(10))
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply