Searching for a hard return (chr(13)??)

[kco1122]

Hi all.. I have a weird question.. is there any way to search a db using a select query for a hard return in a field? Some of my users have been hitting the enter key in multi-line text boxes and those values have been recorded into the database as is.

The problem is that when I utilize that value later in some other way, some of those processes do not like the hard return and spit back a error.

So I'm trying to find a quick way to search for them and eliminate them but I haven't been able to.. any ideas?

I've tried something like:
select displayname from tablename where displayname like %chr(13)%

However sql server doesn't like that.. tells me I'm missing an expression.. any other ideas?

 

[kulrom]

well you can always clean up the Multicolumn textBox from unwanted characters including empty spaces before it's recorded into DB. At least i would do it in that way.
Regards

 

[kco1122]

thx.. I know I can take care of things from here on but there's alot of data already affected and it would be a pain to go through thousands of records if I could find a easier way..

 

[kulrom]

Ok then could you give me an example (concrete) what is that you want to remove ... what signs, chars you have embed inadvertantly???

 

[kco1122]

Here's an example of what I have in there:

"this is test text that someone could type
for any purpose"

What I need to find is basically the 'enter' key or hard return between the word 'type' and the word 'for'. This is the kind of thing I need to find as opposed to what should be there:

"this is test text that someone could type for any purpose"

That help? There are no signs or anything in particular.. I just thought I might be able to search for chr(13) which equates to an 'enter' key press.

 

[kulrom]

Is that MS Access with MEMO datatype or maybe SQL Server with Text datatype ? How do you want to present the text from the field with new line/block inside ? in one row? What there suppose to be fixed? Sorry if i am askign too much but that must be clarified.

 

[kulrom]

Ok as you wasn't fast enough to provide me more info i would suggest using of SQL Replace function (SELECT Replace(Field, expression1, expression2) ...) in your case.
But notice that it is valid T-SQL function while i am not sure will that work against MS Access DB too.

Regards

 

[kco1122]

It's sql server with varchar datatype set at 4000 character (max) .. I want the end result to be all in one row.. essentially replacing the hard return with a space. Np on the questions.

 

[kulrom]

Then you can as i already said go for T-SQL Replace function.

regards

 

[kco1122]

Ok as you wasn't fast enough to provide me more info i would suggest using of SQL Replace function (SELECT Replace(Field, expression1, expression2) ...) in your case.
But notice that it is valid T-SQL function while i am not sure will that work against MS Access DB too.

Regards

Understand that.. will that return me a set of results first so that I can verify to make sure the rows are the ones I want affected? Just to ensure I've got the right data or will that automatically make the change?

 

[kulrom]

it will return data with affected change ... then you can back there and update all those fields with fixed data (if you want)

 

[kco1122]

awesome thx.. I'm already trying it and it looks good.. thx a bunch!!

 

[cjard]

run this in a query manager:

UPDATE myTabl SET myLongVarchar = Replace(Replace(myLongVarChar, char(13), ' '),char(10), ' ')

this will replace any occurrences of char 13 with a space, and then any occurrences of char 10 with a space. Note, you might as a result end up with double spaces. If this is a problem, let me know.


You can ensure that this is an ongoing thing by setting up a trigger on the table. A trigger is a bit of code that is stored on the server and is automatically run when a value is inserted, updated etc.. think of it like an event handler.
In the trigger, you can take the new value (inserts have new values, updates have old values and new values) and replace all the char 13 and char 10 in a similar way to above.

This way, if a user enters anything into the db in future with char 13 and/or char 10, they will automatically get replaced.

 

[cjard]

I've tried something like:
select displayname from tablename where displayname like %chr(13)%

However sql server doesn't like that.. tells me I'm missing an expression.. any other ideas?

it would have been:
select displayname from tablename where displayname like '%' + char(13) + '%'

 

[cjard]

Understand that.. will that return me a set of results first so that I can verify to make sure the rows are the ones I want affected? Just to ensure I've got the right data or will that automatically make the change?

a select statement never modifies data unless it selects a value from a function, and that function modifies data as part of its work. Replace, as a function, does not modify the data in the original table (it cannot, because it might not be fed with data from a table:

Select Replace('foobar', 'o', 'a')