December 6, 2007 at 1:56 am
Hi all,
(SQL 2005)
I have the following problem, can anyone help with some T-SQL?
Thanks
CCB
I need to format paragraphs properly within a column in a table. I need to check that all paragraphs within the field have a double carriage return between them.
The criteria is as follows...
Wherever there is a carriage return, if there is a letter, symbol or digit immediatly before and after it, I need to replace the carriage return with two carriage returns.
December 6, 2007 at 3:11 am
You can use a user defined function that fixes such newline issues. The function could use Patindex and Stuff. A solution:
CREATE FUNCTION dbo.DoubleNewLine ( @text VARCHAR(50) )
RETURNS VARCHAR(50)
AS BEGIN
DECLARE @a VARCHAR(100)
SET @a = '%[a-zA-Z]' + CHAR(13) + '[a-zA-Z]%'
WHILE PATINDEX(@a, @text) > 0
BEGIN
SET @text = STUFF(@text, PATINDEX(@a, @text) + 1, 1,
CHAR(13) + CHAR(13))
END
RETURN @text
END
GO
-- test code:
DECLARE @text VARCHAR(100)
SET @text = 'Hi' + CHAR(13) + CHAR(13) + 'Hello again' + CHAR(13) + 'End'
PRINT dbo.DoubleNewLine(@text)
Regards,
Andras
December 6, 2007 at 3:32 am
Thanks for the response, its along the same lines as I had reached already, however it doesn't handle symbols and numbers. Any suggestions?
CCB
December 6, 2007 at 4:01 am
Charlottecb (12/6/2007)
Thanks for the response, its along the same lines as I had reached already, however it doesn't handle symbols and numbers. Any suggestions?CCB
Just replace the @a with a regexp that matches anything by a newline character. In other words:
SET @a = '%[^' + CHAR(13) + ']' + CHAR(13)+ '[^' + CHAR(13) + ']%'
Regards,
Andras
December 6, 2007 at 4:21 am
Perfect Thanks 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply