Guest
12-17-2004, 12:47 PM
For Issue 2, you should refer to
http://www.joesack.com/RemoveCarriageReturns.htm. This article
contains the following:
Scenario:
You have a data export process from SQL Server to an outside file. The
recipient complains because the text you are sending contains carriage
returns (this could also be any kind of character they think may be
invalid or problematic).
Solution:
If using a varchar, nvarchar, char, or nchar column, Use the UPDATE
statement coupled with the REPLACE and CHAR functions. If using a text
or ntext column, use UPDATETEXT.
How to do it:
For varchar, nvarchar, char, and nchar columns - you can remove the
invalid characters using a combination of functions.
The CHAR function is used to convert an integer value into an ASCII
character. The Carriage return character is represented by CHAR(13),
line feeds are represented by CHAR(10), and Tabs are represented by
CHAR(9).
The REPLACE function is used to search for a specific character or
string within an expression.
In the below UPDATE statement, I am removing all carriage returns from
a varchar field and replacing them with a blank space:
UPDATE Test
SET BlurbVarchar = REPLACE(BlurbVarChar, CHAR(13),' ')
If you do not want to modify the source data, you can instead use a
query that performs the same function using a SELECT statement:
SELECT REPLACE(BlurbVarChar, CHAR(13),' ')
FROM Test
Updating a text file is a little trickier. If you are using
Transact-SQL extensions to update text fields, you may be familiar with
UPDATETEXT. This function will allow us to remove the carriage return
from the text field. The method is slower than REPLACE, because we have
to update one row at a time (and sometimes one row multiple times for
each occurrance of the carriage return).
The example below updates the text field "BlurbText" by removing any
carriage returns. I use a WHILE loop to iterate through each row and
remove the data. You can also use a Cursor if you prefer.
The @PTR variable is used to contain the text pointer, which is
retrieved by using the TEXTPTR function. The @POS variable is used to
identify the location of the carriage return (using CHAR(13)). I use
TOP 1 keyword to retrieve the first row that has a carriage return in
it. It doesn't matter which one, only that I retrieve a specific row
that needs correcting. Lastly, I run UPDATETEXT and then loop around
all over again, until there are no more rows with a carriage return.
-- Keep doing this until all carriage returns are gone
WHILE EXISTS (select * from Test where charindex(char(13),blurbtext)>0)
BEGIN
DECLARE @PTR binary(16)
DECLARE @POS int
-- Grab a single row and the position of the carriage return
SELECT TOP 1
@PTR = TEXTPTR(blurbtext),
@POS = charindex(CHAR(13), blurbtext)-1
FROM Test
WHERE BlurbText LIKE '%' + CHAR(13) +'%'
-- Replace the carriage return with a blank
UPDATETEXT Test.BlurbText @PTR @POS 1 ''
END
http://www.joesack.com/RemoveCarriageReturns.htm. This article
contains the following:
Scenario:
You have a data export process from SQL Server to an outside file. The
recipient complains because the text you are sending contains carriage
returns (this could also be any kind of character they think may be
invalid or problematic).
Solution:
If using a varchar, nvarchar, char, or nchar column, Use the UPDATE
statement coupled with the REPLACE and CHAR functions. If using a text
or ntext column, use UPDATETEXT.
How to do it:
For varchar, nvarchar, char, and nchar columns - you can remove the
invalid characters using a combination of functions.
The CHAR function is used to convert an integer value into an ASCII
character. The Carriage return character is represented by CHAR(13),
line feeds are represented by CHAR(10), and Tabs are represented by
CHAR(9).
The REPLACE function is used to search for a specific character or
string within an expression.
In the below UPDATE statement, I am removing all carriage returns from
a varchar field and replacing them with a blank space:
UPDATE Test
SET BlurbVarchar = REPLACE(BlurbVarChar, CHAR(13),' ')
If you do not want to modify the source data, you can instead use a
query that performs the same function using a SELECT statement:
SELECT REPLACE(BlurbVarChar, CHAR(13),' ')
FROM Test
Updating a text file is a little trickier. If you are using
Transact-SQL extensions to update text fields, you may be familiar with
UPDATETEXT. This function will allow us to remove the carriage return
from the text field. The method is slower than REPLACE, because we have
to update one row at a time (and sometimes one row multiple times for
each occurrance of the carriage return).
The example below updates the text field "BlurbText" by removing any
carriage returns. I use a WHILE loop to iterate through each row and
remove the data. You can also use a Cursor if you prefer.
The @PTR variable is used to contain the text pointer, which is
retrieved by using the TEXTPTR function. The @POS variable is used to
identify the location of the carriage return (using CHAR(13)). I use
TOP 1 keyword to retrieve the first row that has a carriage return in
it. It doesn't matter which one, only that I retrieve a specific row
that needs correcting. Lastly, I run UPDATETEXT and then loop around
all over again, until there are no more rows with a carriage return.
-- Keep doing this until all carriage returns are gone
WHILE EXISTS (select * from Test where charindex(char(13),blurbtext)>0)
BEGIN
DECLARE @PTR binary(16)
DECLARE @POS int
-- Grab a single row and the position of the carriage return
SELECT TOP 1
@PTR = TEXTPTR(blurbtext),
@POS = charindex(CHAR(13), blurbtext)-1
FROM Test
WHERE BlurbText LIKE '%' + CHAR(13) +'%'
-- Replace the carriage return with a blank
UPDATETEXT Test.BlurbText @PTR @POS 1 ''
END