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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-09-19 : 07:38:43
|
| Rashmi writes "Hi, A bug in my front end application inserted a new line character in string values that were saved to a varchar field in the database.e.g. "This is a string" was modified to "This is a \n string" where \n represnts a new line character.1) I want to write a SQL query to cleanse the data by replacing the new line characters in this field (say the field name is reason) with a single space.2) I'd also like to be able to create a SQL query that allows me to specify such strings in the WHERE clause.e.g. SELECT * FROM table WHERE reason = "this is a \n string".Can you please suggest SQL queries to do this?Thanks." |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-09-19 : 07:57:26
|
| Use REPLACE function..UPDATE MyTable SET MyColumn = REPLACE(MyColumn, '\n', CHAR(13) + CHAR(10))Peter LarssonHelsingborg, Sweden |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-19 : 08:06:23
|
| [code]SELECT * FROM table WHERE reason = 'this is a string'[/code]is fine (I changed your double quotes to singles though)However: that will give you a CHAR(13) + CHAR(10) linebreak, and if your data is coming from Unix or somesuch you may only have the CHAR(10), in which case this is also OK:[code]SELECT * FROM table WHERE reason = 'this is a ' + CHAR(10) + ' string'[/code]Kristen |
|
|
|
|
|
|
using strings with embedded new line char in where clause