Cleaning up LF CR from imported foxpro

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

General SQL Server Forums

New to SQL Server Programming

Cleaning up LF CR from imported foxpro

Author

Topic

sherrys
Starting Member

37 Posts

Posted - 2010-03-23 : 14:16:27

I have imported data from foxpro. It has char(10)'s in it. It also has Char(10)+Char(13). Our forms behave properly if they see char(13)+Char(10). If it finds Char(10)+Char(13) it displays 2 squares. If it finds only a char(10), it displays a single square. I am looking for a search and replace to get rid of this.
UPDATE dbo.Comment
SET CommentText = REPLACE (CommentText, CHAR(10)+CHAR(13),CHAR(13)+CHAR(10))
Works for the Char(10) + Char(13) situation.

But then what do I do with the straggling char(10)'s out there. If I replace all Char(10) with Char(13) + Char(10), I will mess up the fixes that I just did.

I was looking into PATINDEX, but it isn't behaving the way that I anticipated.

Any thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-23 : 14:18:55

[code]UPDATE dbo.Comment
SET CommentText = REPLACE (CommentText, CHAR(10),CHAR(13)+CHAR(10))
WHERE CHARINDEX(CHAR(13),CommentText) =0[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sherrys
Starting Member

37 Posts

Posted - 2010-03-23 : 14:35:56

Thanks that fixed some more, but...
There are occurances of char(10) in the same string as char(13)+char(10). So this fix will not find these.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-23 : 16:16:13

First replace the good values with a value that can be found later
replace(CommentText,char(13)+char(10),'~crlf~')
Then replace the remaining char(10) with what you want (for example with empty string...
replace(CommentText,char(10),'')
Then get back your crlf values
replace(CommentText,'~crlf~',char(13)+char(10))

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

sherrys
Starting Member

37 Posts

Posted - 2010-03-24 : 08:26:01

That will do it. Thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-24 : 08:39:31

welcome