removing special characters

I have a large table with a text field that includes line break characters. The line break characters are wreaking havoc on other applications, and they need to be removed and replaced with something more friendly. I found a referenced to a stored procedure that can search and replace all strings in a database, but it isn't quite what I need to do, since it only accepts valid keyboard input and not special characters.

Anyone got any suggestions?You should be able to submit the ASCII value of the special character you want to target.

blindman|||If your text field is a varchar or nvarchar you can simply replace the line breaks when selecting the data from the database.

I.E.

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

Hope this helps..|||can u post some example so that it can be tested and answered!!!!!!!!!!|||Originally posted by smorton
If your text field is a varchar or nvarchar you can simply replace the line breaks when selecting the data from the database.

I.E.

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

Hope this helps..

These are TEXT fields, unfortunately.

Next question: if I can enter the ascii value of the character in the find/replace SP, how do I do so?|||That's what this code is doing for you:

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

The CHAR() function converts and integer value to an ASCII character that the REPLACE function can accept. Some common ASCII values are:
Tab: CHAR(9)
Line feed: CHAR(10)
Carriage return: CHAR(13)

If your data is in TEXT format, you may need to use the CAST function to convert it to VARCHAR first:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

blindman|||Originally posted by blindman
That's what this code is doing for you:

SELECT REPLACE(REPLACE(@.StringToBeSearched,Char(10),' '),Char(13), '')

The CHAR() function converts and integer value to an ASCII character that the REPLACE function can accept. Some common ASCII values are:
Tab: CHAR(9)
Line feed: CHAR(10)
Carriage return: CHAR(13)

If your data is in TEXT format, you may need to use the CAST function to convert it to VARCHAR first:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

blindman

I've been instructed to actually copy this table to an Access database on my desktop, where changes I make won't affect any other applications. So, I need to find an Access solution to the same problem. I'm completely clueless when it comes to Access, s I will probably be doing a LOT of research!|||Originally posted by rscrawford
I've been instructed to actually copy this table to an Access database on my desktop, where changes I make won't affect any other applications. So, I need to find an Access solution to the same problem. I'm completely clueless when it comes to Access, s I will probably be doing a LOT of research!

Kinda like going in the wrong direction...can't they just create a table for you or a small database for you on sql server?

Access was (2002 may be don't know) as feature rich as sql server...|||Originally posted by Brett Kaiser
Kinda like going in the wrong direction...can't they just create a table for you or a small database for you on sql server?


You're right, of course. I duplicated the table in question.

Now, forgive me for being a newbie, but... I have the SELECT statement from blindman:

SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

How do I execute this? I've opened the Query Analyzer, but I don't see where this statement allows for a FROM clause.

Bear in mind, I'm not at all used to SQL Server. Normally, I'd spend a few hours researching on the web, but this particular piece has a priority to it.|||SELECT REPLACE(REPLACE(Cast(@.StringToBeSearched as varchar(8000)),Char(10),' '),Char(13), '')

Replace @.StringToBeSearched with the column name then add your from clause.

I.E.

SELECT REPLACE(REPLACE(Cast(t.ColumnName as varchar(8000)),Char(10),' '),Char(13), '') AS 'ConvertedText'
FROM TableName t

If your column is varchar or nvarchar you do not need to cast the column. If the column is text or ntext the above cast is valid.|||SELECT REPLACE(REPLACE(Cast(t.ColumnName as varchar(8000)),Char(10),' '),Char(13), '') AS 'ConvertedText'
FROM TableName t


Thank you very much!!! That did exactly what I needed it to do!