July 27, 2006 at 8:21 am
I've imported data from Excel which contains 0D when displayed. I use the following to replace the CR.
UPDATE @Main SET
PN=REPLACE(PN, CHAR(13), ' ') -- line feed
FROM @Main
However, when I display the @main temp table it still shows data as follows for a single string. How can I rid myself of that cursed 0D?
004069605 4A116
3 5H860
4X260
July 27, 2006 at 10:44 am
Line feed is actually CHAR(10), while carriage return is CHAR(13).
Try this:
UPDATE @Main SET
PN = REPLACE( REPLACE( PN, CHAR(13), ' '), CHAR(10), ' ')
FROM @Main
I wasn't born stupid - I had to study.
July 27, 2006 at 12:41 pm
Farrell,
I should have posted my cleanup code. As you can see I've tried your recommendation previously and still get hosed text.
UPDATE @Main SET
PN=REPLACE(PN, CHAR(44), '') -- comma
FROM @Main
UPDATE @Main SET
PN=REPLACE(PN, CHAR(39), '') -- single quote
FROM @Main
UPDATE @Main SET
PN=REPLACE(PN, CHAR(38), '') -- exclamation point
FROM @Main
UPDATE @Main SET
PN=REPLACE(PN, CHAR(34), ' ') -- double quote
FROM @Main
UPDATE @Main SET
PN=REPLACE(PN, CHAR(10), ' ') -- carriage return
FROM @Main
UPDATE @Main SET
PN=REPLACE(PN, CHAR(13), ' ') -- line feed
FROM @Main
UPDATE @Main SET
PN=REPLACE( REPLACE(PN, CHAR(13), ' '), CHAR( 10), ' ') -- 0D?
FROM @Main
Steve
July 27, 2006 at 2:09 pm
We use a table and a function
dbo.ReplaceSpecialCharacters
( @ValueToAlter AS varchar(2000)) RETURNS varchar(2000)
AS
BEGIN
SELECT @ValueToAlter = REPLACE( @ValueToAlter, SearchForCharacter, ReplacementCharacter) FROM SpecialCharacters
RETURN ( @ValueToAlter )
END
The table SpecialCharacters contains:
CREATE TABLE [dbo].[SpecialCharacters] (
[RowID] [int] IDENTITY (1, 1) NOT NULL,
[SearchForCharacter] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ReplacementCharacter] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Explanation] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL)
That way you can keep adding as new odd characters come up if your user is pasting into a text box. So far we have had very good luck with catching oddities. You may have oddity like "end of text", "end of transmission", "escape" or something else and it is much easier to add to a table than keep coding.
Also, I highly recommend using the Explanation field! Especailly for Newbies or fields that do not display well in Query Analyzer...
Good luck!
I wasn't born stupid - I had to study.
July 27, 2006 at 5:44 pm
Farrell,
Thanks! That seems to be working well.
Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply